Welcome to MSSQL Forum!
MSSQL Forum - Discussions about Microsoft SQL Server

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

Case statement not working

Discussion in 'SQL Server Scripts and Tools' started by Makis Best, Oct 24, 2012.

  1. Makis Best

    Makis Best New Member

    Hi all

    I have this Case statement but seems not working, can anyone help with the script?

    Code (text):
    Select LItmSale.name,
    CASE LItmSale.LinkID
        WHEN 001 THEN (SELECT Price FROM ItemPrc WHERE PriceListAA = 39 AND PriceCtgr = 'Team1' AND ServiceID = 100.001)
        WHEN 002 THEN (SELECT Price FROM ItemPrc WHERE PriceListAA = 39 AND PriceCtgr = 'Team1' AND ServiceID = 100.002)
        WHEN 003 THEN (SELECT Price FROM ItemPrc WHERE PriceListAA = 39 AND PriceCtgr = 'Team1' AND ServiceID = 100.003)
        WHEN 004 THEN (SELECT Price FROM ItemPrc WHERE PriceListAA = 39 AND PriceCtgr = 'Team1' AND ServiceID = 100.004)
        WHEN 005 THEN (SELECT Price FROM ItemPrc WHERE PriceListAA = 39 AND PriceCtgr = 'Team1' AND ServiceID = 100.005)
        WHEN 006 THEN (SELECT Price FROM ItemPrc WHERE PriceListAA = 39 AND PriceCtgr = 'Team1' AND ServiceID = 100.006)
        WHEN 008 THEN (SELECT Price FROM ItemPrc WHERE PriceListAA = 39 AND PriceCtgr = 'Team1' AND ServiceID = 100.008)
        WHEN 016 THEN (SELECT Price FROM ItemPrc WHERE PriceListAA = 39 AND PriceCtgr = 'Team1' AND ServiceID = 100.016)
        WHEN 017 THEN (SELECT Price FROM ItemPrc WHERE PriceListAA = 39 AND PriceCtgr = 'Team1' AND ServiceID = 100.017)
        WHEN 018 THEN (SELECT Price FROM ItemPrc WHERE PriceListAA = 39 AND PriceCtgr = 'Team1' AND ServiceID = 100.018)
        ELSE 0
    END as VALUES
    from litmsale
    I get no error
    The selects return null when I run the script.
    When I run every select in parenthesis individual the return value is correct.
    If I replace any select with the number 1 then the script runs OK and returns the value 1 correct but when run with the select returns null.

    Please help.
    Thank you
  2. Peter Schmitz

    Peter Schmitz Administrator Staff Member

    I tested this with some testdata, and apart from having to wrap the "VALUES" in squarte brackets (i.e. [VALUES]), the script worked fine for me.The brackets are required due to VALUES being a reserved SQ Server keyword.

    One thing that might cause some error is that the value of ServiceID contains a period (.). What data type did you define the column as?
  3. Makis Best

    Makis Best New Member

    The column data are Int.
    I try your propulsive and it works.

    Thank you

Share This Page