ansaurus

Question

Return custom value if join not successfull ?

Answer 1

+1 A:

(when a blank '' or a '-' is encountered in column)

If a join is unsuccessful, the column value will be null (not blank or hyphen).

Maybe this works better

  a.deptcode || '  ' || coalesce (b.deptname, '<missing>') as fulldeptname

Thilo 2010-10-12 06:53:30

Thanks, is using COALESCE preferred over CASE for comparing nulls ? If yes, does it give any performance advantage ?

Popo 2010-10-12 07:02:00

CASE WHEN b.deptname IS NULL should work, too. Not sure about performance impact (if anything COALESCE should be faster, because it is "simpler" and less general) , but COALESCE is shorter to type (if you can remember how to spell it) and easier to read.

Thilo 2010-10-12 07:10:43

Popo 2010-10-12 07:28:33

for more complex cases, you can use CASE/WHEN. Why do you need to check for "-" ?

Thilo 2010-10-12 07:33:31

Some rows have '-' also. I am still looking to return alternate values with COALESCE(in case of '' COlumnA else ColumnB).

Popo 2010-10-12 07:37:04

COALESCE can only check for null. In Oracle, you'd have DECODE as well, but in DB2 I guess you are stuck with CASE (which should work, though, so no problem, right).

Thilo 2010-10-12 07:40:27

Yep I have got CASE working fine.

Popo 2010-10-12 07:48:37

ansaurus

tags:

views:

answers:

Return custom value if join not successfull ?

related questions