Avatar of AbeSpain
AbeSpain
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Problem getting a case clause to work

I have the following code but it does nto work, I think it is something to do with the Round and sum functions inside the case clause.

      SELECT    
            Case When @JobsInvoiceVatiD = 1 then
                  Round(TPP.PartsPaidAmount,2)
            Else
                  Round(Sum(TPP.PartsPaidAmount / 1.175),2)
            end  As JobsSubTotal,            
            Round(Sum(TPP.PartsPaidAmount) - Sum(TPP.PartsPaidAmount / 1.175),2) As JobsVat,
            Sum(TPP.PartsPaidAmount) As JobsTotal
      FROM        
            tblPartsType AS TPT INNER JOIN
            tblParts AS TP ON TPT.PartsTypeiD = TP.PartsTypeiD INNER JOIN
            tblJobs AS TJ INNER JOIN
            tblPartsPayment AS TPP ON TJ.JobsiD = TPP.JobsiD ON TP.PartsiD = TPP.PartsiD INNER JOIN
            tblClients AS TC ON TJ.ClientsiD = TC.ClientsiD INNER JOIN
            tblJobsStatus AS TJS ON TJ.JobsStatusiD = TJS.JobsStatusid
      Where
            TJ.jobsid = @jobsid
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Computer101

8/22/2022 - Mon
Aneesh

any error ?
Aneesh

are u getting any error ?
AbeSpain

ASKER
TPP.PartsPaidAmount is valid in the select clause type error.

I fixed it in the end by adding a group by clause on PartsPaidAmount. I dont know why though. Is it somethign to do with the round and sum functions?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Lowfatspread

explain what you are trying to do?

Case When @JobsInvoiceVatiD = 1 then
                  Round(TPP.PartsPaidAmount,2)
            Else
                  Round(Sum(TPP.PartsPaidAmount / 1.175),2)
            end  As JobsSubTotal,

why is the first part not in a SUM?

since it isn't in a sum (aggregate function) then from a sql point of view partspaidamount needs to be in a group by clause.

ASKER CERTIFIED SOLUTION
Computer101

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.