Post reply

Error - Column OINV.DocRate is invalid in the select list

  • January 10, 2020 at 3:08 pm

    #3714398

    Hi

    I have below query but getting error - Column 'OINV.DocRate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    SELECT T0.[DocNum], T3.[ChapterID], Sum(T1.[Price]), Sum(T1.[LineTotal]), Max(T1.[TaxCode]) ,
    ISNULL((Select (case when sum(T5.RvsChrgTax)<=0 then sum(T5.TaxSum)/T0.DocRate else 0 end) From INV4 T5 Where T5.DocEntry = T0.DocEntry and T5.staType = -100 ),0) [CGSTAmt], 
    ISNULL((Select (case when sum(T5.RvsChrgTax)<=0 then sum(T5.TaxSum)/T0.DocRate else 0 end) From INV4 T5 Where T5.DocEntry = T0.DocEntry and T5.staType = -110 ),0) [SGSTAmt],
    ISNULL((Select (case when sum(T5.RvsChrgTax)<=0 then sum(T5.TaxSum)/T0.DocRate else 0 end) From INV4 T5 Where T5.DocEntry = T0.DocEntry and T5.staType = -120 ),0) [IGSTAmt]
    FROM OINV T0 
    INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
    INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] 
    INNER JOIN OCHP T3 ON T1.[HsnEntry] = T3.[AbsEntry] 
    LEFT OUTER JOIN INV4 T5 on T1.[DocEntry]=T5.[DocEntry] and T1.[LineNum]=T5.[LineNum] AND T5.LineSeq = 0
    LEFT OUTER JOIN INV3 T6 ON T0.DocEntry = T6.DocEntry
    WHERE T0.[DocDate] between '2019/12/01' and '2020/01/31' 
    group by T0.[DocNum], T3.[ChapterID],T0.[DocEntry]

    Thanks

    • This topic was modified 4 years, 11 months ago by  jsshivalik.
    • This topic was modified 4 years, 11 months ago by  jsshivalik.

  • January 10, 2020 at 3:25 pm

    #3714422

    Changing those three CASE expressions so they say sum(T5.TaxSum/T0.DocRate) should resolve the error.  Whether it actually gives the results you're looking for - well, that's something you'll want to check carefully.

    John

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply