If statement in SQL server 1

[dt2demar]

I have some queries that I wrote in MS Access and I'm trying to convert them to SQL server syntax. In access, the SQL code is as follows:

select distinct
TableA.Field1
TableA.Field2
IF(TableA.Field3 like "X","1",IF(TableA.Field3,"Y","2",TableA.Field3))
From... etc.

What is the equivalent SQL syntax for a nested IF loop such as this? Thanks.

 

[TomSark]

I think this might do it, unless I misinterpreted your question:

select distinct
TableA.Field1
TableA.Field2
CASE
WHEN TableA.Field3 like "X" THEN "1"
ELSE
CASE WHEN TableA.Field3 like "Y" THEN "2"
ELSE
TableA.Field3
END
END
From... etc.

Thanks,

Tom

 

[dt2demar]

Thanks. I forgot to add an important line to my SQL, it's
'AS FIELD4'

select distinct
TableA.Field1
TableA.Field2
IF(TableA.Field3 like "X","1",IF(TableA.Field3,"Y","2",TableA.Field3)) AS Field4

from etc...

Basically I'm trying to read in data from Field3, modify it depending on certain conditions and output as Field4. So field4 is really field3 modified. Anyways, how can I achieve this with your CASE suggestions. Thanks.

 

[TomSark]

select distinct
TableA.Field1,
TableA.Field2,
CASE
WHEN TableA.Field3 like "X" THEN "1"
ELSE
CASE WHEN TableA.Field3 like "Y" THEN "2"
ELSE
TableA.Field3
END
END AS Field4
From... etc.

 

[dt2demar]

Thanks. It works fine. However, I am having problems converting a bigger nested loop:

IF(TableA.Field3 like "X","1",IF(TableA.Field3,"Y","2",IF(TableA.Field3 like "Z","3",IF(TableA.Field3 like "ZZ","4,"TableA.Field3)))) AS Field4

I tried extending your suggestion to:

CASE
WHEN TableA.Field3 like "X" THEN "1"
ELSE
CASE
WHEN TableA.Field3 like "Y" THEN "2"
ELSE
CASE
WHEN TableA.Field3 like "Z" THEN "3"
ELSE
CASE
WHEN TableA.Field3 like "ZZ" THEN "4"
ELSE
TableA.Field3
END
END AS Field4

but this doesn't work. What am I missing for this case?

thanks again! You're a big help.

 

[TomSark]

Try this...

CASE
WHEN TableA.Field3 like "X" THEN "1"
WHEN TableA.Field3 like "Y" THEN "2"
WHEN TableA.Field3 like "Z" THEN "3"
WHEN TableA.Field3 like "ZZ" THEN "4"
ELSE
TableA.Field3
END AS Field4

Tom

 

[dt2demar]

thanks. It works!