case statement

[ted_amare]

I am comapring accid from previous month and current month.
IF accid existed in previous month, but does not exist in the current month
THEN 'Closed'. ELSE
IF accid did not exist last month but exists in the current month
THEN 'New'
ELSE
'Old'"

The following query is not working pls. advise!
select
CMN.accid as current_month,PMN.accid as prev_month,
case
when select PMN.accid from prev_month_table PMN minus select CMN.accid from current_month_table CMN then 'Closed'
when select CMN.accid from current_month_table CMN minus select accid from prev_month_table then 'New'
else 'Old'
end as acc_status
from current_month_table, prev_month_table

[RB185048]

May be you can try something like below

sel
Coalesce ( CMN.accid ,PMN.Accid) as ACCID,
CASE
when CMN.accid is null and PMN.Accid is not null then 'Closed'
when CMN.accid is not null and PMN.Accid is null then 'NEW'
when CMN.accid is not null and PMN.Accid is not null then 'OLD' END as acc_status
from
CMN
full outer join
PMN
on CMN.accid = PMN.accid ;

hope this solves your problem

[ted_amare]

That answer is perfect and prompt! Thanks