CODEWITHSUNDEEP
sqldatabasedb2
jcrshankar
jcrshankar

Reputation: 1196

when one column is null use other column in where db2

I have two table i need to intersect the common records.

table1
Name epmno date1            date2
ss1   123  01/10/2017     01/10/2017
ss2   124  02/10/2019     01/10/2019
ss3   125  null           01/09/2017

table1 condition : select if date1 > 18 months , if date1 was null then use date2 to check > 18 months

table2
   epmno date3            
   123  01/10/2017     
   124  02/10/2019    
   125  null

table2 condition : select if date3 > 18 months and date3 is null.

to join table1 and table2 I have common field epmno( so i an use where table1.epmno=table2.empno)

query:

select empno, name from (select empno,Name from table1  where (date1 is null use date2) > 
current_date - 18 MONTHS) a
(select empno from table2 where date3 > current_date - 18 MONTHS or date3 is null) b where 
a.empno=b.empno;

result:

Name epmno 
ss1   123
ss3   125

how can we write optimal query in this case using db2 syntax , could you please help me out?

Upvotes: 1

Views: 460

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13006

use coalesce() function. then left join your table2

select t1.empno, t1.name 
from table1 t1      
left join table2 t2 on t2.empno = t1.empno
where
    coalesce(coalesce(t1.date1, t1.date2), t2.date3) > current_date - 18 MONTHS) a

Upvotes: 1

Related Questions