locked
Error - Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression. 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.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.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.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] group by T0.DocEntry,T3.[ChapterID]

    Thanks

    Sunday, January 12, 2020 3:10 PM

All replies

  • User-719153870 posted

    Hi jsshivalik,

    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.RelateType = 1),0)

    The error should be caused by these three lines, seems you are comparing multiple records to a single record.

    You can try to change these three to static value, such as 0, and run the query again see if the problem occurs again, if not then i'm right that these three lines caused the problem.

    In this case, we will need your stuctures and sample data of all your 6 tables to reproduce the problem, or if you can reproduce the problem with a simple demo would be better.

    You can refer to this and seems you need to change your code like below:

    Notice: this is not tested.

    ISNULL((Select (case when sum(T5.RvsChrgTax)<=0 then sum(T5.TaxSum/T0.DocRate) else 0 end) From INV4 T5 inner join on T5.DocEntry = T0.DocEntry where T5.staType = -100 and T5.RelateType = 1),0)

    Best Regard,

    Yang Shen

    Monday, January 13, 2020 9:11 AM