Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (SQL 8.0)

    Corden try this:

    SELECT
    QUOTENAME(ISNULL(tPerson.EmployeeID,''),'"') AS EmployeeID,
    QUOTENAME(tPerson.LastName,'"') AS LastName,
    QUOTENAME(ISNULL(tPerson.FirstName,''),'"') AS FirstName,
    QUOTENAME(ISNULL(tPerson.MiddleName,''),'"') AS MiddleName,
    QUOTENAME(iif([StatusCode] = 'ACT' ,Null,iif([StatusCode] = 'TMP' ,'Temporary' ,'Unknown'))) AS NewStatus,
    QUOTENAME('','"') AS Reserved25
    From tPerson
    LEFT JOIN tPersonStatusHist psh
    ON tperson.PersonGuid = psh.PersonGuid
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  2. #2
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (SQL 8.0)

    That didn't work for me. I got this message.

    Server: Msg 170, Level 15, State 1, Line 6
    Line 6: Incorrect syntax near '='.

    Any other suggestion?

  3. #3
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    SQL Statement (SQL 8.0)

    Superfluous empty lines (>20) removed by HansV

    Any Sql people out there-

    I need help with a sql statement. What I would like to do is evaluate a field by adding an if then statement and adding a new column name called new status. See Below for sql statement:
    SELECT
    QUOTENAME(ISNULL(tPerson.EmployeeID,''),'"') AS EmployeeID,
    QUOTENAME(tPerson.LastName,'"') AS LastName,
    QUOTENAME(ISNULL(tPerson.FirstName,''),'"') AS FirstName,
    QUOTENAME(ISNULL(tPerson.MiddleName,''),'"') AS MiddleName,
    QUOTENAME(Case psh.StatusCode When psh.StatusCode = 'ACT' then null (Iam trying to do this)
    When psh.StatusCode = 'TMP' then 'Temporary' ELSE 'Unknown')AS NewStatus,
    QUOTENAME('','"') AS Reserved25
    From tPerson
    LEFT JOIN tPersonStatusHist psh
    ON tperson.PersonGuid = psh.PersonGuid

    Any help would be highly appreciated! <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL Statement (SQL 8.0)

    In general you cannot use the Immediate IF statement in SQL Server - you must resort to Case statements and the basic IF construct. T-SQL is nowhere near as friendly as Jet SQL, although you can write UDFs (User Defined Functions) to accomplish many of the things you've grown to expect out of Access.
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (SQL 8.0)

    Sorry Corden,

    I didn't realize you couldn't use IIF in SQL Server.

    Thanks Wendell.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •