CASE WHEN db_1.appl_no = ' '
THEN db_2.appl_no
ELSE db_1.appl_no END as Appl_nbr
Looks neater!
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, ........)
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.
Hi ,
I think you can try below.
( COALESCE(NULLIF ( db_1.appl_no,'') ,db_2.appl_no)
regards,
rupesh
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