Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    3

    Unanswered: CASE WHEN Statement Help

    Hi Guys,
    I'm new to SQL and trying to feel my way around and get this done.

    Below is the SQL statement which I'm running.

    SELECT TOP 500 LEFT(ProcCode, 2) AS TwoDigitCode, ProcCode, MsgType AS 'Response Code', TxAmount, TxCashout,
    CASE LEFT(ProcCode, 2)
    WHEN '00' AND MsgType != '800' OR MsgType != '810'
    THEN 'PURCHASE'
    WHEN '01' THEN 'CASHOUT'
    WHEN '03' THEN 'Authorisation'
    WHEN '09' THEN 'PurCash'
    WHEN '17' THEN 'VOIDPAYFROM'
    WHEN '18' THEN 'VOIDPAYTO'
    WHEN '20' THEN 'REFUND'
    WHEN '21' THEN 'PAYTO'
    WHEN '28' THEN 'VOIDPAYFROM'
    WHEN '31' THEN 'BALANCE'
    END AS 'Calculdated value'
    FROM [Test].[dbo].[test_database]
    This is the error I get:
    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'AND'.


    Any help would be greatly appreciated!!!

    Thanks in advance

  2. #2
    Join Date
    Aug 2011
    Posts
    3
    I tried the following way as well without any success.

    SELECT TOP 500 LEFT(ProcCode, 2) AS TwoDigitCode, ProcCode, MsgType AS 'Response Code', TxAmount, TxCashout,
    CASE LEFT(ProcCode, 2)
    WHEN (SELECT LEFT(ProcCode, 2) FROM [Test].[dbo].[test_database]
    WHERE (MsgType != '800') OR (MsgType != '810'))
    THEN 'PURCHASE'
    WHEN '01' THEN 'CASHOUT'
    WHEN '03' THEN 'Authorisation'
    WHEN '09' THEN 'PurCash'
    WHEN '17' THEN 'VOIDPAYFROM'
    WHEN '18' THEN 'VOIDPAYTO'
    WHEN '20' THEN 'REFUND'
    WHEN '21' THEN 'PAYTO'
    WHEN '28' THEN 'VOIDPAYFROM'
    WHEN '31' THEN 'BALANCE'
    END AS 'Calculdated value'
    FROM [Test].[dbo].[test_database]
    I get the following error
    Msg 512, Level 16, State 1, Line 1
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT TOP 500 LEFT(ProcCode, 2) AS TwoDigitCode
         , ProcCode
         , MsgType AS 'Response Code'
         , TxAmount
         , TxCashout
         , CASE WHEN LEFT(ProcCode, 2) = '00' 
                 AND MsgType NOT IN ( '800' , '810' )
                THEN 'PURCHASE'
                WHEN LEFT(ProcCode, 2) = '01' THEN 'CASHOUT'
                WHEN LEFT(ProcCode, 2) = '03' THEN 'Authorisation'
                WHEN LEFT(ProcCode, 2) = '09' THEN 'PurCash'
                WHEN LEFT(ProcCode, 2) = '17' THEN 'VOIDPAYFROM'
                WHEN LEFT(ProcCode, 2) = '18' THEN 'VOIDPAYTO'
                WHEN LEFT(ProcCode, 2) = '20' THEN 'REFUND'
                WHEN LEFT(ProcCode, 2) = '21' THEN 'PAYTO'
                WHEN LEFT(ProcCode, 2) = '28' THEN 'VOIDPAYFROM'
                WHEN LEFT(ProcCode, 2) = '31' THEN 'BALANCE'
            END AS 'Calculdated value'
      FROM [Test].[dbo].[test_database]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2011
    Posts
    3
    Your a genius r937!!!!
    That's exactly what I needed...
    Thank you

Tags for this Thread

Posting Permissions

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