I am extracting two columns using a case statement and selecting them into one single column

Question

I have two columns named pno and rno both are varchar which has a order number and now my requirement is

if rno is not null it should print Actual Generated, If its not then its Forecast Generated.

if pno is not null then it should print Reconciled else Forecast Generated.

my query is :

 select  
       case
        when (pno!=null) then 'Reconciled'
       when (pno=null) then 'Forecast1 Generated'
       when (rno=null) then 'Forecast Generated'
       when (rno!=null) then 'Actual Generated'
       end as Status
 from tablexyz

When i am executing it i only get either pno or rno case statements result set.my expected out put should return all the case statements. I could not use and in case statement condition, as i need all the records. I run this on Dbvisualizer.

Thanks in advance.

Answer 1

You should use pno is not null instead of pno!=null in SQL.SQL uses three valued logic, and pno!=null is UNKNOWN, which is not true nor false, but as it is not true, the case is not taken.

So your statement should look like

select  
       case
       when (pno is not null) then 'Reconciled'
       when (pno is null) then 'Forecast1 Generated'
       when (rno is null) then 'Forecast Generated'
       when (rno is not null) then 'Actual Generated'
       end as Status
 from tablexyz