CODEWITHSUNDEEP
t-sqlsql-server-2012caseiif
eflores89
eflores89

Reputation: 359

Translating IIF query to Transact-SQL

so I've been trying to migrate my DB to SQL-2012 form Access-2010.

Most of the tables and queries are ok, but I am having trouble with this bit: 

 IIf([mkt_Original].[IMPOEXPO]="1",IIf([prod]="0201",IIf(([USD]/[Tons])
   <[TCambio].[CortePrecio0201],1,0),IIf([prod]="0202",IIf(([USD]/[Tons])
   <[TCambio].[CortePrecio0202],1,0),1)),1) AS GPrecio,

So I tried CASE;

 CASE WHEN [mkt_Original].[IMPOEXPO]="1", 
THEN
 CASE WHEN [rod]="0201" 
    THEN
      CASE WHEN 
      [USD]/[Tons])<[TCambio].[CortePrecio0201] 
      THEN 1 
    ELSE 0 
ELSE 
    CASE WHEN
    [prod]="0202"
    THEN
        CASE WHEN  
        [USD]/[Tons])<[TCambio].[CortePrecio0202]
        THEN 1
    ELSE 0
    ELSE 1
    END
AS GPrecio,

I keep getting a "Wrong Syntax near CASE" when I try to run it. Any thing I might be missing? Thanks in advance!

Upvotes: 1

Views: 136

Answers (3)

Chains
Chains

Reputation: 13157

CASE Statements are like this: CASE WHEN THEN ELSE END

So if you nest them, you have to END each nested CASE.

You can also format and simplify your code just a bit...

CASE
   WHEN [mkt_Original].[IMPOEXPO]="1", <--Remove the comma 
      THEN CASE
           WHEN [rod]="0201" AND [USD]/[Tons])<[TCambio].[CortePrecio0201] 
              THEN 1 
           WHEN [prod]="0202" AND [USD]/[Tons])<[TCambio].[CortePrecio0202] 
              THEN 1
           ELSE 0
           END
   ELSE 1
END
AS GPrecio,

Upvotes: 0

bjnr
bjnr

Reputation: 3437

Use ' (apostrophe) instead of " (quotation mark) and don't forget to add an "END" for each case statement:

CASE WHEN condition THEN value1 ELSE value2 END

or

CASE variable
    WHEN value1 THEN value2
    WHEN value3 THEN value4
    ELSE value 3
END

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280429

SQL Server 2012 now supports IIF, so why bother translating to a much more verbose CASE expression? All you really need to do is change double-quotes (") to single-quotes (') - or remove them if, e.g., IMPOEXPO is a numeric column.

IIf([mkt_Original].[IMPOEXPO]='1',IIf([prod]='0201',IIf(([USD]/[Tons])
  <[TCambio].[CortePrecio0201],1,0),IIf([prod]='0202', 
  IIf(([USD]/[Tons])<[TCambio].[CortePrecio0202],1,0),1)),1) AS GPrecio,

Upvotes: 2

Related Questions