locked
SQL Query If Then Else RRS feed

  • Question

  • I can't seem to find the right way to write this:

    SELECT Type

    FROM tblEXT

    If tblEXT.Type = 'Auto' THEN 86.88

    If tblEXT.Type = 'Field' THEN 103.17

    If tblEXT.Type = any thing else THEN 65.16

    END AS [Paid]

    Any help would be appreciated.

    Thanks!


    Gee

    Sunday, August 26, 2012 9:35 PM

Answers

  • You'll also want to move your AS CASE PAID to alias the return from the case statement.

    SELECT Type, 
    CASE Type
    	WHEN 'Auto' THEN 86.88
    	WHEN 'Field' THEN 103.17
    	ELSE 65.16
    END AS 'CASE PAID'
    FROM tblEXT

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Sunday, August 26, 2012 10:22 PM

All replies

  • Hi Gee, this looks like a good query to write with a CASE statement.  Something like:

    SELECT Type,
    CASE Type WHEN 'Auto' THEN 86.88 WHEN 'Field' THEN 103.17 ELSE 65.16 END FROM tblEXT

    Thanks,
    Sam Lester (MSFT)

    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.


    Sunday, August 26, 2012 9:51 PM
  • It says Incorrect syntax near the keyword 'SELECT'

    SELECT Type

    CASE Type

    WHEN 'Auto' THEN 86.88

    WHEN 'Field' THEN 103.17

    ELSE 65.16

    END

    FROM tblEXT

    AS [CASE PAID]


    Gee

    Sunday, August 26, 2012 10:15 PM
  • It looks like you are missing the comma after the SELECT Type in the first line.  It should be SELECT Type,

    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Sunday, August 26, 2012 10:19 PM
  • You'll also want to move your AS CASE PAID to alias the return from the case statement.

    SELECT Type, 
    CASE Type
    	WHEN 'Auto' THEN 86.88
    	WHEN 'Field' THEN 103.17
    	ELSE 65.16
    END AS 'CASE PAID'
    FROM tblEXT

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Sunday, August 26, 2012 10:22 PM
  • I made those changes and it's still telling me "Incorrect syntax near the keywork 'SELECT'


    Gee

    Sunday, August 26, 2012 10:28 PM
  • Can you paste in your full query?  And which version of SQL Server are you using?


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.


    Sunday, August 26, 2012 10:30 PM
  • I see what I did...

    I had tblEXT already selected at the top.

    I removed it and it looks like its going to work!

    Thanks!


    Gee

    • Marked as answer by GretaF Monday, August 27, 2012 12:18 AM
    • Unmarked as answer by GretaF Monday, August 27, 2012 12:18 AM
    Sunday, August 26, 2012 10:33 PM