#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    4
    Rep 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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    4
    Rep Power
    0
    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.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    117
    Rep Power
    9
    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

IMN logo majestic logo threadwatch logo seochat tools logo