|
|
|
Junior Member
 
Group: Forum Members
Last Login: 11/11/2009 1:12:32 PM
Posts: 14,
Visits: 39
|
|
I need to write a query, so that it returns the non-null value like the following,
COALESCE (db_1.appl_no, db_2.appl_no) as Appl_nbr
But, db_1.appl_no has both '?' and ' '
here the coalesce function takes '?' as Null, but returns the blank field ' ' assuming it to be Not Null.
so i change the query like the following,
COALESCE (CASE WHEN db_1.appl_no = ' '
THEN NULL
ELSE db_1.appl_no END , db_2.applno as appl.no) as Appl_nbr
Is there any other better way???
Pls help
-Thank you
-Thank you
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
CASE WHEN db_1.appl_no = ' '
THEN db_2.appl_no
ELSE db_1.appl_no END as Appl_nbr
Looks neater!
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 11/11/2009 1:12:32 PM
Posts: 14,
Visits: 39
|
|
Jimm,
thanks for the reply.
here the problem is i need to pick the not null value between ---> COALESCE (db_1.appl_no, db_2.appl_no)
but the db_1.appl_no itself has three possibilites ---> Null value (?), Blank value (' ') & also a Variable number in it('211234423').
the coalesce considers the blank field (' ') in db_1.appl_no as not null and picks it ,
instead of picking the number value in the db_2.appl_no
so is there any other way instead of using coalesce (case when ' ' then .. end, ........)
-Thank you
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
No
CASE db_1.appl_no
WHEN ' '
THEN db_2.appl_no
WHEN NULL
THEN db_2.appl_no
ELSE db_1.appl_no END as Appl_nbr
If you look at the explain of a COALESCE, it is simply a short form of CASE.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 2 days ago @ 9:26:30 PM
Posts: 6,
Visits: 11
|
|
Hi ,
I think you can try below.
( COALESCE(NULLIF ( db_1.appl_no,'') ,db_2.appl_no)
regards,
rupesh
Rupesh Baheti
Datawarehouse Analyst
OCBC Bank
Singapore
|
|
|
|