Home » SQL & PL/SQL » SQL & PL/SQL » Plsql using NVL (19.2)
Plsql using NVL [message #686272] Tue, 19 July 2022 14:21 Go to next message
Unclefool
Messages: 82
Registered: August 2021
Member
Apologies for the question but I can't seem to get NVL function working in my example below. If the location_id is NULL, as it is in the second row, I want to print 'N/A'.


CREATE TABLE departments (department_id, department_name, manager_id, location_id) AS
SELECT 100, 'Finance', 108,  1700 FROM DUAL UNION ALL
SELECT 120, 'Treasury', NULL,  1700 FROM DUAL;

BEGIN 
  FOR rec in (SELECT * from departments)
loop
dbms_output.put_line(rec.department_id||' '||rec.department_name||' '||NVL(rec.manager_id,'N/A')||' '||rec.location_id);
  end loop;
END;

Re: Plsql using NVL [message #686273 is a reply to message #686272] Tue, 19 July 2022 14:37 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You need to think about data types. What data type is departments.manager_id and what data type is 'N/A' ?
orclz> ed
Wrote file afiedt.buf

  1  BEGIN
  2    FOR rec in (SELECT * from departments)
  3  loop
  4  dbms_output.put_line(rec.department_id||' '||rec.department_name||' '||NVL(to_char(rec.manager_id),'N/A')||' '||rec.location_id);
  5    end loop;
  6* END;
orclz> /
100 Finance 108 1700
120 Treasury N/A 1700

PL/SQL procedure successfully completed.
Just another example of bugs caused by relying on implicit type casting Smile
Re: Plsql using NVL [message #686274 is a reply to message #686273] Tue, 19 July 2022 15:01 Go to previous message
Unclefool
Messages: 82
Registered: August 2021
Member
Thanks
Previous Topic: Integrity constraint violation
Next Topic: Need to derive YTD,MAT,MQT
Goto Forum:
  


Current Time: Thu Mar 28 20:54:16 CDT 2024