I'm running a SELECT statement on the form below:
SELECT col1 ||'|'|| col2 ||'|'|| col3
This gives me the following outprint, which is what I need
The problem is when col2 is returning a NULL value, then I get this:
When what i want is this:
Is there any way to get my query results to print NULL value as (null)?
I've tried the NVL function, NVL(col2, 'null') but this won't work since there's a mismatch on datatypes. I can get the NULL value printed by not concatenating the columns and save the results as csv, but that does not give me the formatting I want.
You can convert the Number column to Character and then check for NVL.
with data as ( select 'col1' col1, 1 col2, 'col3' col3 from dual union all select 'col1' col1, null col2, 'col3' col3 from dual ) select col1 || '|' || nvl(to_char(col2), 'null') || '|' || col3 from data; COL1||'|'||NVL(TO_CHAR(COL2),'NULL')||'|'||COL3 -------------------------------------------------- col1|1|col3 col1|null|col3
The reason why you got an error message has to do with automatic datatype conversions, that oracle will do.
the concatanation "varchar2||number||varchar2" compares two different datatypes. Since a string is expected for the || operator to work, the number will be implicitly converted to varchar2. Meaning the database executes: varchar2||TO_CHAR(number)||varchar2. Including default nls conversion.
The NVL function works slightly differently. It is overloaded to accept several datatypes, like varchar2, number, date and some more.
That means there are at least 3 versions
The parser now looks at the first parameter. In your case that was a number. That means the NVL(number,number) return number version is used.
The second parameter that you added is a string. In this case oracle tries to implicitly convert this string into a number. But this failed (to_number('null') => error) and you got the error message.
If this string parameter can be converted into a number, then the statement will succeed.
select 'ABC'||NVL(cast(null as number), '123') || 'DEF' from dual; ABC123DEF
What oracle does here, are TWO implicit datatype conversions.
first conversion: change the second parameter of nvl from string to number ('123' => 123).
second conversion change the number output of nvl from number to string (123 => '123')
Of cause that is a lot of unessesary stuff to do. Best is ALWAYS to use explizit datatype conversion.