CODEWITHSUNDEEP
sql-server-2008
Anil Patel
Anil Patel

Reputation: 3

How to convert SQL query with IIF inside AND to work on MS SQL 2008 Server

I have a SQL query which is Used in EXCEL Connection on data imported from CSV file which keeps changing on regular interval. The Where Clause in Query is:

"WHERE (ColumnA<-.4) AND (iif(ColumnB<500 And ColumnC>-900 And ColumnD>-900,0,1)>0) OR (iif(ColumnB<2000 And ColumnE+ColumnF>10000,1,0)>0)"

This above query works well inside Excel, now I Imported my CSV file to SQL 2008 Server.. but its not working due to iif function is not availble in 2008 server.

I need help to convert this query to work on SQL 2008 Server.

Thanks in advance.

Upvotes: 0

Views: 62

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46231

You can refactor IIF as a CASE expression like below. This will work in all SQL Server versions.

ColumnA < -.4 
AND (
    CASE WHEN ColumnB<500 And ColumnC>-900 And ColumnD>-900 THEN 0 ELSE 1 END > 0
    OR CASE WHEN ColumnB<2000 And ColumnE+ColumnF>10000 THEN 1 ELSE 0 END > 0)

The searched form of the CASE expression used here evaluates the WHEN condition and returns the THEN value when true or the ELSE value of false. The END keyword indicates the end of the CASE expression.

Each CASE expression result in this query is checked to see if it's greater than zero. If either result is greater than zero (because of the OR logical operator) the predicate enclosed in parenthesis is true and evaluated with the other WHERE clause predicate (ColumnA < -.4).

Upvotes: 1

Related Questions