CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
HELP_ME
HELP_ME

Reputation: 2729

Case statement issue in stored procedure

Here is the part of my select query that I am trying to use the case statement 

CASE SoftripNetTest.dbo.ResGeneral.ResStatus
   WHEN 'X' THEN CAST(SoftripNetTest.dbo.fn_GetXMLValue(SoftripNetTest.dbo.fn_GetXMLValue(AdditionalData, 'HeaderCancellationData', ''), 'TotalQuotePrice', 0) AS DECIMAL(18, 2)) 
           + CAST(SoftripNetTest.dbo.fn_GetXMLValue(SoftripNetTest.dbo.fn_GetXMLValue(AdditionalData, 'HeaderCancellationData', ''), 'TotalQuotePriceTax', 0) AS DECIMAL(18, 2)) 
           + CAST(SoftripNetTest.dbo.fn_GetXMLValue(SoftripNetTest.dbo.fn_GetXMLValue(AdditionalData, 'HeaderCancellationData', ''), 'TotalQuotePriceGST', 0) AS DECIMAL(18, 2)) 
       as total                       
   ELSE  (SUM(SoftripNetTest.dbo.ResGeneral.TTlQuotePrice + SoftripNetTest.dbo.ResGeneral.TTlQuotePriceTax + SoftripNetTest.dbo.ResGeneral.TTlQuotePriceGST)  as total
END

And this is the entire query

SELECT     
    SoftripNetTest.dbo.ResGeneral.ResNumber, SoftripNetTest.dbo.ResGeneral.LeadName, 
    SoftripNetTest.dbo.ResGeneral.NoPax, SoftripNetTest.dbo.ResGeneral.MarketCode, 
    SoftripNetTest.dbo.ResGeneral.ResProductCode, SoftripNetTest.dbo.ResGeneral.ResTourCode, 
    SoftripNetTest.dbo.ResGeneral.SellMethod, SoftripNetTest.dbo.ResGeneral.DepDate, 
    CONVERT(datetime, SoftripNetTest.dbo.ResGeneral.BookDate, 101) AS bookdate, 
    SoftripNetTest.dbo.ResGeneral.BookedBy, 
    CASE SoftripNetTest.dbo.ResGeneral.ResStatus
       WHEN 'X' THEN CAST(SoftripNetTest.dbo.fn_GetXMLValue(SoftripNetTest.dbo.fn_GetXMLValue(AdditionalData, 'HeaderCancellationData', ''), 'TotalQuotePrice', 0) AS DECIMAL(18, 2)) 
          + CAST(SoftripNetTest.dbo.fn_GetXMLValue(SoftripNetTest.dbo.fn_GetXMLValue(AdditionalData, 'HeaderCancellationData', ''), 'TotalQuotePriceTax', 0) AS DECIMAL(18, 2)) 
          + CAST(SoftripNetTest.dbo.fn_GetXMLValue(SoftripNetTest.dbo.fn_GetXMLValue(AdditionalData, 'HeaderCancellationData', ''), 'TotalQuotePriceGST', 0) AS DECIMAL(18, 2)) 
       ELSE  (SUM(SoftripNetTest.dbo.ResGeneral.TTlQuotePrice + SoftripNetTest.dbo.ResGeneral.TTlQuotePriceTax + SoftripNetTest.dbo.ResGeneral.TTlQuotePriceGST) 
    END,
    SoftripNetTest.dbo.Product.ProductName, SoftripNetTest.dbo.ResGeneral.ParentResNumber, 
    SoftripNetTest.dbo.ResGeneral.ResStatus, 
    dbo.ResItinerary_insurance.LocalCost * dbo.ResItinerary_insurance.Qty AS insurance
FROM         
    SoftripNetTest.dbo.ResGeneral 
INNER JOIN
    SoftripNetTest.dbo.Product ON SoftripNetTest.dbo.ResGeneral.ResProductCode = SoftripNetTest.dbo.Product.ProductCode 
LEFT OUTER JOIN
    dbo.ResItinerary_insurance ON SoftripNetTest.dbo.ResGeneral.ResNumber = dbo.ResItinerary_insurance.ResNumber
WHERE     
    (SoftripNetTest.dbo.ResGeneral.BookDate BETWEEN @startdate AND @enddate)
GROUP BY 
    SoftripNetTest.dbo.ResGeneral.ResNumber, SoftripNetTest.dbo.ResGeneral.LeadName, 
    SoftripNetTest.dbo.ResGeneral.NoPax, SoftripNetTest.dbo.ResGeneral.MarketCode, 
    SoftripNetTest.dbo.ResGeneral.ResProductCode, SoftripNetTest.dbo.ResGeneral.ResTourCode, 
    SoftripNetTest.dbo.ResGeneral.SellMethod, SoftripNetTest.dbo.ResGeneral.DepDate, $
    SoftripNetTest.dbo.ResGeneral.BookDate, SoftripNetTest.dbo.ResGeneral.BookedBy, 
    SoftripNetTest.dbo.ResGeneral.BookSource, SoftripNetTest.dbo.Product.ProductName, 
    SoftripNetTest.dbo.ResGeneral.ParentResNumber, SoftripNetTest.dbo.ResGeneral.ResStatus,

I keep getting errors near the AS in my case statement? Can case statements be used in the middle of a query? I want the everything to be pulled the same way for both resstatus A and X except the total would this be the best way to do it? I am using fn_getxmlvalue which is a function, because the cancelled information is stored in xml.

Upvotes: 0

Views: 376

Answers (4)

clyc
clyc

Reputation: 2450

remove the "as total" alias from the following line:

ELSE  (SUM(SoftripNetTest.dbo.ResGeneral.TTlQuotePrice + SoftripNetTest.dbo.ResGeneral.TTlQuotePriceTax + SoftripNetTest.dbo.ResGeneral.TTlQuotePriceGST)  as total
        END

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

Move the as total alias after the END of the CASE statement.

         CASE SoftripNetTest.dbo.ResGeneral.ResStatus
       WHEN 'X' THEN CAST(SoftripNetTest.dbo.fn_GetXMLValue(SoftripNetTest.dbo.fn_GetXMLValue(AdditionalData, 'HeaderCancellationData', ''), 'TotalQuotePrice', 0) AS DECIMAL(18, 2)) 
                          + CAST(SoftripNetTest.dbo.fn_GetXMLValue(SoftripNetTest.dbo.fn_GetXMLValue(AdditionalData, 'HeaderCancellationData', ''), 'TotalQuotePriceTax', 0) AS DECIMAL(18, 2)) 
                          + CAST(SoftripNetTest.dbo.fn_GetXMLValue(SoftripNetTest.dbo.fn_GetXMLValue(AdditionalData, 'HeaderCancellationData', ''), 'TotalQuotePriceGST', 0) AS DECIMAL(18, 2)) 
       ELSE  (SUM(SoftripNetTest.dbo.ResGeneral.TTlQuotePrice + SoftripNetTest.dbo.ResGeneral.TTlQuotePriceTax + SoftripNetTest.dbo.ResGeneral.TTlQuotePriceGST)
    END   as total

Upvotes: 2

rlb.usa
rlb.usa

Reputation: 15041

         CASE SoftripNetTest.dbo.ResGeneral.ResStatus
       WHEN 'X' THEN CAST(SoftripNetTest.dbo.fn_GetXMLValue(SoftripNetTest.dbo.fn_GetXMLValue(AdditionalData, 'HeaderCancellationData', ''), 'TotalQuotePrice', 0) AS DECIMAL(18, 2)) 
                          + CAST(SoftripNetTest.dbo.fn_GetXMLValue(SoftripNetTest.dbo.fn_GetXMLValue(AdditionalData, 'HeaderCancellationData', ''), 'TotalQuotePriceTax', 0) AS DECIMAL(18, 2)) 
                          + CAST(SoftripNetTest.dbo.fn_GetXMLValue(SoftripNetTest.dbo.fn_GetXMLValue(AdditionalData, 'HeaderCancellationData', ''), 'TotalQuotePriceGST', 0) AS DECIMAL(18, 2)) 

       ELSE  (SUM(SoftripNetTest.dbo.ResGeneral.TTlQuotePrice + SoftripNetTest.dbo.ResGeneral.TTlQuotePriceTax + SoftripNetTest.dbo.ResGeneral.TTlQuotePriceGST)  as total
    END 
    as total

You can't name individual parts of the case statement, but you can name the outcome column, just move your name to the end of the case statement

Upvotes: 2

JNK
JNK

Reputation: 65217

Just remove the as total in the middle of the CASE.

The CASE statement will output one field at the end, and you name it AFTER THE END:

CASE WHEN ...
     WHEN ...
     ELSE ...
END as 'Blah'

Upvotes: 0

Related Questions