locked
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. RRS feed

  • 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

    Sunday, January 12, 2020 3:09 PM

All replies

  • 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

    Monday, January 13, 2020 7:57 AM
  • 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 
    Wednesday, January 29, 2020 11:49 AM