subquery inside case...urgent..please help..

[nsaxena]

Hi All ,

I have requirement like: we have two tables 1) maintable and 2)cityreference.  Cityreference is table with column name as mastercity.

main table has one column  city column as city . I am using a view based on maintable in which i need to derive new column
column calculation is like :

for each row in maintable if city(column in maintable) is not part of all  mastercity(i.e city in maintable does not exists in city in cityreference table) then 'a'
else b

I understand that we need  join cityreference table and main table using common field(city). But i am unable to write sql above calculation....

Please suggest sql which will give me desired result.
Many thanks in advance!!

[charlie]

You could try doing a left/right outer join using city for the join b/w maintable and cityreference. Then add a case statetement
case when cityreference.city is null then 'a'
else 'b' end

Is this what you were looking for?
Charlie

[nsaxena]

Hi Charlie....thanks...i tried that...it worked!!