case statement - multiple columns

[eddiec]

Hello Folks,

I want to write a case statement which is conditional on a number of fields, so that if the value of any of the fields is zero then the result is FALSE.

I am trying this;
SELECT stuff....
(CASE WHEN (UNIT = '0' or NUM = '0' or STREET = '0' or POBOX= '0' or SUBURB= '0'
or POSTCODE= '0' or STATE= '0')
THEN 'FALSE'
ELSE 'TRUE'


END) match,
FROM tables...

But I get this error;
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(CASE WHEN (UNIT = '0' or NUM = '0' or STREET = '0' or POBOX= '0' or SUBURB= '0'
or POSTCODE= '0' or STATE= '0')
THEN 'FALSE'
ELSE 'TRUE'
END) match'.

Help!

eddiec :-)

[Rod]

Don't do much Transact-SQL but I think the CASE statement should be:

CASE (UNIT = '0' or NUM = '0' or STREET = '0' or POBOX= '0' or SUBURB= '0' or POSTCODE= '0' or STATE= '0')
WHEN True
THEN 'FALSE'
ELSE 'TRUE'
END

Are you sure you're testing for string zero?

[Rod]

No on second thoughts yours should work but perhaps you should be coding:

...
'Match' = CASE
WHEN (.......)
THEN 'FALSE'
ELSE 'TRUE'
END