CODEWITHSUNDEEP
sqloracle-database
JimmyN
JimmyN

Reputation: 599

use NVL in LEFT JOIN oracle?

I have 2 tables:

Table A:      TableB:
ID            ID    Value
1             2     0.5
2             3     10
3

my query: 

SELECT A.ID,B.VALUE FROM A LEFT JOIN B ON A.ID = B.ID;

result:

ID    Value
1      
2     0.5
3     10

I want to replace the null value with '-' and I tried using NVL (B.VALUE, '-'), but that doesn't work so what should I do?

Upvotes: 0

Views: 2560

Answers (1)

eaolson
eaolson

Reputation: 15094

"Doesn't work" isn't a very helpful description of your problem. My guess is you got "ORA-01722: invalid number", because NVL( B.value, '-' ) will try to cast the second argument to the same datatype as the first and you can't cast "-" to a number. But if you wrap the first argument in TO_CHAR, it works, as in:

SELECT A.ID, NVL( TO_CHAR( B.VALUE ), '-' ) FROM TableA A LEFT JOIN TableB B ON A.ID = B.ID;

SQL Fiddle example

Upvotes: 6

Related Questions