Case statement help

[globalwarmer]

Here is the query i was able to build in sql server ....i wanted an equivalent access syntax...tried everthing but nothing seems to work...it is a urgent need at my work and i need the help....

SELECT A.ID,A.Name,
CASE WHEN A.SAL = ( SELECTDISTINCT SAL2 FROM (
SELECTDISTINCT T1.* ,T2.SAL AS SAL2,
CASE WHEN T1.SAL = T2.SALTHEN 'EQUAL' ELSE 'OTHER' END ASTEST
FROM #TEMP_TAB1 T1 CROSSJOIN #TEMP_TAB2 T2 ) A WHERETEST = 'EQUAL') THEN 0
ELSE A.SAL END AS
OLD_SAL,
CASE WHEN B.SAL = ( SELECTDISTINCT SAL2 FROM (
SELECTDISTINCT T1.* ,T2.SAL AS SAL2,
CASE WHEN T1.SAL = T2.SALTHEN 'EQUAL'
ELSE 'OTHER'END AS TEST FROM #TEMP_TAB1 T1
CROSS JOIN #TEMP_TAB2 T2 ) A WHERE TEST = 'EQUAL')
THEN 0 ELSE B.SAL END AS
NEW_SAL
FROM #TEMP_TAB1 A, #TEMP_TAB2 B
WHERE A.ID = B.ID AND
A.NAME = B.name

 

[Guus2005]

if i was the parser confronted by this query i'd say it was too complex.
obviously it is too complex for you also.

You need to use subqueries to simplify the main query.

HTH

 

[ByteMyzer]

In SQL-Server syntax:
CASE WHEN T1.MyField = '0' THEN 'None' WHEN T1.MyField = '1' THEN 'One' ELSE 'Many' END

...in Microsoft Access, you can use the Switch function:
Switch(T1.MyField = '0', 'None', T1.MyField = '1', 'One', True, 'Many')