Error - Column 'INV1.LineNum' is invalid in the select list

Question

Post reply

Error - Column 'INV1.LineNum' is invalid in the select list

jsshivalik

SSChasing Mays

Points: 612

January 11, 2020 at 5:04 am

#3714632

Hi

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.

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

Viewing 4 posts - 1 through 3 (of 3 total)

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

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Kathi Kellenberger SSChasing Mays Points: 601 More actions · Answer 2

It's happening in your subqueries. SQL is treating the value from the outer query as part of the SELECT so the aggregate rules apply. Will adding T1.LineNum to the GROUP BY invalidate your results?

ScottPletcher SSC Guru Points: 100942 More actions · Answer 3

SQL can't execute the query as you've written it because there may be multiple values of T1.LineNUM for a given combination of DocEntry and ChapterID.

For example, suppose these two rows result from all the JOINs:

DocEntryChapterID LineNum

1, 1, 10

1, 1, 20

When the subquery for CGSTAmt, for example, specifies "T5.LineNum = T1.LineNum" which T1.LineNum value should SQL use? What if there were a hundred different LineNum values? SQL will not report wrong results, so instead it rejects the form of the query, because it's invalid.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.