Thursday, March 22, 2012

conditions in where clause

Hi I am upsizing the access database to SQL 2005.
I currently converting Access Query's to SQL stored procs and functions but i have got stuck on one issue.

In access there is a update statement that has an IIF in the where clause, i have tried replicating this in T-SQL buy using case statement but it does not work.

in access the where looks some thing like this Where IIF(table1.Column1 = 0,Table2.column1,Table3.Column1)

Any one know how i can best replicate this behavior?

Dagz

In place of IIF, use CASE. Using your example:

Where IIF(table1.Column1 = 0,Table2.column1,Table3.Column1)

Code Snippet


WHERE CASE Table1.Column1
WHEN 0 THEN Table2.Column1
ELSE Table3.Column1
END

|||Here you go

Code Snippet


Case WHEN table1.Column1 = 0 THEN Table2.Column1
else Table3.Column1
END|||

when i try this i get the error message, where is of none boolean type?

No comments:

Post a Comment