Case statement not working

Makis Best

Makis Best

New Member
Hi all

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

Code: 
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
 
P

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?
 
You must log in or register to reply here.
Top