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

• jsshivalik

  SSChasing Mays

  Points: 612

  More actions

  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 5 years, 5 months ago by jsshivalik.
  • This topic was modified 5 years, 5 months ago by jsshivalik.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  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