Error - Column 'INV1.LineNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Question

User-797751191 posted

Hi

SELECT T0.DocEntry,T3.[ChapterID], Sum(T1.[LineTotal]), 
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 and T5.LineNum = T1.LineNum and T5.RelateType = 1),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 and T5.LineNum = T1.LineNum and T5.RelateType = 1),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 and T5.LineNum = T1.LineNum and T5.RelateType = 1),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
group by T0.DocEntry,T3.[ChapterID]

Thanks

Answer 1

User288213138 posted

Hi jsshivalik,

SELECT T0.DocEntry,T3.[ChapterID], Sum(T1.[LineTotal]), 
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 and T5.LineNum = T1.LineNum and T5.RelateType = 1),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 and T5.LineNum = T1.LineNum and T5.RelateType = 1),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 and T5.LineNum = T1.LineNum and T5.RelateType = 1),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
group by T0.DocEntry,T3.[ChapterID]

Please show me your table structure, otherwise I cannot reproduce your problem.

Column 'INV1.LineNum' is invalid in the select list because it is not contained in either...

Here is a same error for your reference: https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e

Best regards,

Sam

Answer 2

User49201036 posted

While using GroupBY Clause ,select query without aggregate function should be added in group by clause .

In your case select query i.e. staType,LineNum and RelateType should be added in group by clause

SELECT T0.DocEntry,T3.[ChapterID], Sum(T1.[LineTotal]), 
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 and T5.LineNum = T1.LineNum and T5.RelateType = 1),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 and T5.LineNum = T1.LineNum and T5.RelateType = 1),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 and T5.LineNum = T1.LineNum and T5.RelateType = 1),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
group by T0.DocEntry,T3.[ChapterID],staType,LineNum,RelateType