COALESCE

Database
Highlighted
Enthusiast

COALESCE

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
4 REPLIES
Enthusiast

Re: COALESCE

CASE WHEN db_1.appl_no = ' '
THEN db_2.appl_no
ELSE db_1.appl_no END as Appl_nbr

Looks neater!
Enthusiast

Re: COALESCE

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, ........)
Enthusiast

Re: COALESCE

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.

Enthusiast

Re: COALESCE

Hi ,

I think you can try below.

( COALESCE(NULLIF ( db_1.appl_no,'') ,db_2.appl_no)

regards,
rupesh