Printing "NULL" in a SELECT statement?

[Netherbeast]

When I do a SELECT statement and the value is null, is there a way to print "NULL" for that value?

I dont see a way to do this without an IF statement and I have not come across ways to declare an IF statement.

Thank in advance

 

[ddrillich]

Please try: select nvl('','NULL') from dual;

Regards,
Dan

 

[ddrillich]

A closer solution to the IF statement would look like:

select
case dummy
when '' then 'NULL' else
dummy
end
from dual

 

[Netherbeast]

Thanks for the reply.
I did what you said and I get errors:

SELECT NVL(",'NULL') FROM COMPUTER;
ERROR: MISSING DOUBLE QUOTE IN IDENTIFIER

This is the full SELECT statement im using:

SELECT
RPAD(C.COMPUTERNAME, 9) COMPUTER,
RPAD(P.LAST, 9) ASSIGNEDTO
FROM COMPUTER C LEFT OUTER JOIN PERSON P
ON C.PERSONID = P.PERSONID;

I want P.LAST to print NULL if no one is assigned to a computer.

Thanks Dan

 

[sem]

You used " (double quote, CHR(34)), while in original post were 2 single quotes (CHR(39)):


SELECT
RPAD(C.COMPUTERNAME, 9) COMPUTER,
RPAD(NVL(P.LAST,'NULL'), 9) ASSIGNEDTO
FROM COMPUTER C LEFT OUTER JOIN PERSON P
ON C.PERSONID = P.PERSONID;

Regards, Dima

http://seminihin.narod.ru

 

[wannaBDBA]

hi Nether!
If u r using SQL*plus env. then u can also change NULL variable by SET NULL "NULL".Although its a temporary solution until that session ends but it will surely show u "NULL" for every null values of diff columns u encounter in ur SELECT statement!

i hope this would work for u!
Gregi!

 

[Netherbeast]

Dima and Gregi,

you guys ROCK, thanks for the help and explinations.

Thank you