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!!
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
Hi Charlie....thanks...i tried that...it worked!!