IIF in SQL Query Analyzer

[Jeff Johnson]

My query hinges on (among other things) an instant if statement:

SELECT IIf([CorP]="P",-1,1) AS Factor ...

I'm using the ID from two tables: Customers and Prospects. If it's a prospect
I make the key negative. My UNION statement combines both tables-- but the
prospects key is made negative there. That way I can make the join. (Is
this a standard technique?)

My query works fine in Access... but when I bring it in to SQL 6.5 I get
"Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '='."

Is there a way to do IIF in SQL 6.5 Query Analyzer?


PS What's the best book for learning SQL if you're moving from Access to
MS SQL server?

[Qbert]

It has been a while since I've used SQL server, have you tried replacing the
"'s with 's?. I seem to remember that there is a difference in SQL Server...


"Jeff Johnson" <johnsonjs@hotmail.com> wrote:
>
>My query hinges on (among other things) an instant if statement:
>
>SELECT IIf([CorP]="P",-1,1) AS Factor ...
>
>I'm using the ID from two tables: Customers and Prospects. If it's a prospect
>I make the key negative. My UNION statement combines both tables-- but
the
>prospects key is made negative there. That way I can make the join. (Is
>this a standard technique?)
>
>My query works fine in Access... but when I bring it in to SQL 6.5 I get
> "Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '='."
>
>Is there a way to do IIF in SQL 6.5 Query Analyzer?
>
>
>PS What's the best book for learning SQL if you're moving from Access to
>MS SQL server?

[Craig Clearman]

Jeff,

>Is there a way to do IIF in SQL 6.5 Query Analyzer?

You need to use the case statement:

SELECT CASE CorP WHEN 'P' THEN -1 ELSE 1

Ciao, Craig

[Craig Clearman]

Hi Craig,

Don't forget the END:

SELECT CASE CorP WHEN 'P' THEN -1 ELSE 1 END ...

Ciao, Craig