IIF in SQL Query Analyzer


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: IIF in SQL Query Analyzer

  1. #1
    Jeff Johnson Guest

    IIF in SQL Query Analyzer


    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?

  2. #2
    Qbert Guest

    Re: IIF in SQL Query Analyzer


    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?



  3. #3
    Craig Clearman Guest

    Re: IIF in SQL Query Analyzer

    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


  4. #4
    Craig Clearman Guest

    Re: IIF in SQL Query Analyzer

    Hi Craig,

    Don't forget the END:

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

    Ciao, Craig


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center