locked
Trying to implement a case statement but getting a syntax error "WHEN" RRS feed

  • Question

  • I ultimatly want to update "Value" with the ones returned in the following attempt at a select, but getting a syntax error:

    SELECT DetailTypeCode, 
    	CASE 
    		WHEN DetailTypeCode = 'ALL201' AND  Value = 0 THEN 2684 ELSE Value
    		WHEN DetailTypeCode = 'ALL208' AND  Value = 0 THEN 135 ELSE Value
    		WHEN DetailTypeCode = 'ILN201' AND  Value = 0 THEN 15 ELSE Value
    		WHEN DetailTypeCode = 'REV201' AND  Value = 0 THEN 2684 ELSE Value
    		WHEN DetailTypeCode = 'REV301' AND  Value = 0 THEN 36403 ELSE Value
    		WHEN DetailTypeCode = 'REV401' AND  Value = 0 THEN 33719 ELSE Value
    		WHEN DetailTypeCode = 'REV403' AND  Value = 0 THEN 7 ELSE Value
    	END as Value
    FROM CreditReportSummaryDetail
    WHERE ReportSummaryID in
    (
    	SELECT c.ReportSummaryID 
    	FROM orderdetailproduct odp 
    	JOIN dbo.CreditReportSummary c ON  odp.orderid=c.orderid and odp.ReportID=c.DataSourceID
    	WHERE odp.OrderId=13100034 AND DetailTypeCode in ('ALL201','ALL208','ILN201','REV201','REV301','REV401','REV403')
    )
    order by DetailTypeCode

    Monday, June 3, 2013 6:34 PM

Answers

  • remove all "ELSE Value" and keep the last one, like this:

    SELECT DetailTypeCode, 
    	CASE 
    		WHEN DetailTypeCode = 'ALL201' AND  Value = 0 THEN 2684 
    		WHEN DetailTypeCode = 'ALL208' AND  Value = 0 THEN 135 
    		WHEN DetailTypeCode = 'ILN201' AND  Value = 0 THEN 15 
    		WHEN DetailTypeCode = 'REV201' AND  Value = 0 THEN 2684 
    		WHEN DetailTypeCode = 'REV301' AND  Value = 0 THEN 36403 
    		WHEN DetailTypeCode = 'REV401' AND  Value = 0 THEN 33719 
    		WHEN DetailTypeCode = 'REV403' AND  Value = 0 THEN 7 
    		ELSE Value
    	END as Value
    FROM CreditReportSummaryDetail
    WHERE ReportSummaryID in
    (
    	SELECT c.ReportSummaryID 
    	FROM orderdetailproduct odp 
    	JOIN dbo.CreditReportSummary c ON  odp.orderid=c.orderid and odp.ReportID=c.DataSourceID
    	WHERE odp.OrderId=13100034 AND DetailTypeCode in ('ALL201','ALL208','ILN201','REV201','REV301','REV401','REV403')
    )
    order by DetailTypeCode


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    • Proposed as answer by Naomi N Monday, June 3, 2013 6:42 PM
    • Marked as answer by BillyM2010 Monday, June 3, 2013 6:48 PM
    Monday, June 3, 2013 6:37 PM
  • I'm not sure exactly what you want (sample data and the result you want from that sample data might make your question clearer), but perhaps

    SELECT DetailTypeCode, 
    	CASE 
    		WHEN DetailTypeCode = 'ALL201' AND  Value = 0 THEN 2684
    		WHEN DetailTypeCode = 'ALL208' AND  Value = 0 THEN 135
    		WHEN DetailTypeCode = 'ILN201' AND  Value = 0 THEN 15
    		WHEN DetailTypeCode = 'REV201' AND  Value = 0 THEN 2684
    		WHEN DetailTypeCode = 'REV301' AND  Value = 0 THEN 36403
    		WHEN DetailTypeCode = 'REV401' AND  Value = 0 THEN 33719
    		WHEN DetailTypeCode = 'REV403' AND  Value = 0 THEN 7 
    		ELSE Value
    	END as Value
    FROM CreditReportSummaryDetail
    WHERE ReportSummaryID in
    (
    	SELECT c.ReportSummaryID 
    	FROM orderdetailproduct odp 
    	JOIN dbo.CreditReportSummary c ON  odp.orderid=c.orderid and odp.ReportID=c.DataSourceID
    	WHERE odp.OrderId=13100034 AND DetailTypeCode in ('ALL201','ALL208','ILN201','REV201','REV301','REV401','REV403')
    )
    order by DetailTypeCode

    Tom

    • Proposed as answer by Naomi N Monday, June 3, 2013 6:43 PM
    • Marked as answer by BillyM2010 Monday, June 3, 2013 6:49 PM
    Monday, June 3, 2013 6:38 PM

All replies

  • remove all "ELSE Value" and keep the last one, like this:

    SELECT DetailTypeCode, 
    	CASE 
    		WHEN DetailTypeCode = 'ALL201' AND  Value = 0 THEN 2684 
    		WHEN DetailTypeCode = 'ALL208' AND  Value = 0 THEN 135 
    		WHEN DetailTypeCode = 'ILN201' AND  Value = 0 THEN 15 
    		WHEN DetailTypeCode = 'REV201' AND  Value = 0 THEN 2684 
    		WHEN DetailTypeCode = 'REV301' AND  Value = 0 THEN 36403 
    		WHEN DetailTypeCode = 'REV401' AND  Value = 0 THEN 33719 
    		WHEN DetailTypeCode = 'REV403' AND  Value = 0 THEN 7 
    		ELSE Value
    	END as Value
    FROM CreditReportSummaryDetail
    WHERE ReportSummaryID in
    (
    	SELECT c.ReportSummaryID 
    	FROM orderdetailproduct odp 
    	JOIN dbo.CreditReportSummary c ON  odp.orderid=c.orderid and odp.ReportID=c.DataSourceID
    	WHERE odp.OrderId=13100034 AND DetailTypeCode in ('ALL201','ALL208','ILN201','REV201','REV301','REV401','REV403')
    )
    order by DetailTypeCode


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    • Proposed as answer by Naomi N Monday, June 3, 2013 6:42 PM
    • Marked as answer by BillyM2010 Monday, June 3, 2013 6:48 PM
    Monday, June 3, 2013 6:37 PM
  • I'm not sure exactly what you want (sample data and the result you want from that sample data might make your question clearer), but perhaps

    SELECT DetailTypeCode, 
    	CASE 
    		WHEN DetailTypeCode = 'ALL201' AND  Value = 0 THEN 2684
    		WHEN DetailTypeCode = 'ALL208' AND  Value = 0 THEN 135
    		WHEN DetailTypeCode = 'ILN201' AND  Value = 0 THEN 15
    		WHEN DetailTypeCode = 'REV201' AND  Value = 0 THEN 2684
    		WHEN DetailTypeCode = 'REV301' AND  Value = 0 THEN 36403
    		WHEN DetailTypeCode = 'REV401' AND  Value = 0 THEN 33719
    		WHEN DetailTypeCode = 'REV403' AND  Value = 0 THEN 7 
    		ELSE Value
    	END as Value
    FROM CreditReportSummaryDetail
    WHERE ReportSummaryID in
    (
    	SELECT c.ReportSummaryID 
    	FROM orderdetailproduct odp 
    	JOIN dbo.CreditReportSummary c ON  odp.orderid=c.orderid and odp.ReportID=c.DataSourceID
    	WHERE odp.OrderId=13100034 AND DetailTypeCode in ('ALL201','ALL208','ILN201','REV201','REV301','REV401','REV403')
    )
    order by DetailTypeCode

    Tom

    • Proposed as answer by Naomi N Monday, June 3, 2013 6:43 PM
    • Marked as answer by BillyM2010 Monday, June 3, 2013 6:49 PM
    Monday, June 3, 2013 6:38 PM