Case statement not working

Dev Shed Forums Sponsor:

October 23rd, 2012, 05:34 AM

makis_best

Registered User

Join Date: Jun 2012

Posts: 4

Time spent in forums: 1 h 28 m 20 sec
Reputation Power: 0

Case statement not working

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.

Please help.
Thank you

October 23rd, 2012, 05:54 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,369

Time spent in forums: 3 Months 1 Week 2 Days 6 h 12 m 47 sec
Reputation Power: 4140

Code:

SELECT LItmSale.name
     , ( SELECT Price FROM ItemPrc 
          WHERE PriceListAA = 39 
            AND PriceCtgr = 'Team1' 
            AND ServiceID = 100 + ( LItmSale.LinkID / 1000.0 ) AS values
  FROM litmsale

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

October 23rd, 2012, 06:53 AM

makis_best

Registered User

Join Date: Jun 2012

Posts: 4

Time spent in forums: 1 h 28 m 20 sec
Reputation Power: 0

Quote:

Originally Posted by r937

Code:

SELECT LItmSale.name
     , ( SELECT Price FROM ItemPrc 
          WHERE PriceListAA = 39 
            AND PriceCtgr = 'Team1' 
            AND ServiceID = 100 + ( LItmSale.LinkID / 1000.0 ) AS values
  FROM litmsale

Thank you for the help but the script doesn't change anything.
It doesn't return any value or error.
The same result as before.

The question is why i don't get the results of the select but when I change them with the value 1 the the script returns the values 1 correct.

October 26th, 2012, 02:44 PM

gk53

Contributing User

Join Date: Oct 2012

Posts: 71

Time spent in forums: 8 h 28 m 25 sec
Reputation Power: 8

it looks like your ServiceID is not numeric
try

Code:

SELECT LItmSale.name
     , ( SELECT sum(Price) FROM ItemPrc 
          WHERE PriceListAA = 39 
            AND PriceCtgr = 'Team1' 
            AND ServiceID = '100.' + cast(LItmSale.LinkID as varchar(3))  AS values
  FROM litmsale

Case statement not working

Developer Shed Advertisers and Affiliates