IIf(xxx,yyy,zzz) in SQL 1

[christheprogrammer]

Hiya, I have a query which was built in ACCESS and it contains:

SELECT
[table1].[x] AS CHOICE_VALUE,
IIf([table1].[x] = 2, [table1].[y], 1) AS CHOICE
FROM [table1]

The IIf statement means:

If([table1].[x] = 2){
SELECT [table1].[y] AS CHOICE
}else{
SELECT 1 AS CHOICE
}

How do I do this in (TRANSACT) SQL using only one query? I know it can be done by using 2 queries, one of which is an UPDATE query but I would like to do it in one query.. TIA! Chris says: "It's time for a beer."

 

[tlbroadbent]

Use the Case function in Transact-SQL.

SELECT
[table1].[x] AS CHOICE_VALUE,
Case When [table1].[x] = 2
Then [table1].[y]
Else 1 End AS CHOICE
FROM [table1]

I recommend posting Transact-SQL questions in forum181 - Microsoft SQL Server or forum187 - Sybase: Adaptive Server. Use the forum for the product you use. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[christheprogrammer]

Thanks Terry,
It worked like a charm...

Cheers Chris says: "It's time for a beer."