locked
Oact.GroupMask' 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 T1.Account, Case when (T0.GroupMask = 4 or T0.GroupMask = 5) and @AsonDate >= '2019/04/01' then (Select Sum(T1.Debit) - Sum(T1.Credit) from Jdt1 where RefDate >= '2019/04/01' and RefDate <= @AsOnDate) else Sum(T1.Debit) - Sum(T1.Credit) end as Balance
    			from Oact T0
    			inner join JDT1 T1 on T0.AcctCode = T1.Account
    			inner join OJDT T2 on T1.TransId = T2.TransId
    			where T2.RefDate <= @AsOndate
    			group by T1.Account

    Thanks

    Tuesday, December 17, 2019 8:39 AM

All replies

  • User452040443 posted

    Hi,

    I did not understand your query but try adding the T0.GroupMask column in the Group By to see if you got the expected result.

    Hope this help

    Tuesday, December 17, 2019 12:30 PM
  • User-719153870 posted

    Hi jsshivalik,

    The problem part can be translated as: select Sum(T1.Debit) - Sum(T1.Credit) from table JDT1 when another table Oact's field GroupMask's value = 4 or 5 group by Account field of table JDT1.

    This query is trying to use the When as the where in sql and it will throw error when you are also using the group by at the same time.

    A direct solution to this problem is to add max() method to your field T0.GroupMask, like in below demo:

    create table #Oact
    (
    AcctCode int,
    GroupMask int
    )
    
    create table #JDT1
    (
    Account int,
    Debit int,
    Credit int,
    TransId int,
    RefDate date
    )
    
    create table #OJDT
    (
    TransId int,
    RefDate date
    )
    
    insert into #OJDT values(1,'2019-12-17')
    insert into #Oact values(1,4)
    insert into #JDT1 values(1,100,32,1,'2019-12-17')
    
    declare @AsonDate date=getdate()
    Select T1.Account, Case when (max(T0.GroupMask) = 4 or max(T0.GroupMask) = 5) and @AsonDate >= '2019/04/01' then (Select Sum(T1.Debit) - Sum(T1.Credit) from #Jdt1 where RefDate >= '2019/04/01' and RefDate <= @AsOnDate) else Sum(T1.Debit) - Sum(T1.Credit) end as Balance
    			from #Oact T0
    			inner join #JDT1 T1 on T0.AcctCode = T1.Account
    			inner join #OJDT T2 on T1.TransId = T2.TransId
    			where T2.RefDate <= @AsOndate
    			group by T1.Account

    However, this solution is blind to the final result you expected. Only if you shared the tables' information( at least like i did in my demo) and expected result that we can help you solve the true problem rather than the one you met currently in case you are using the wrong query.

    Best Regard,

    Yang Shen

    Wednesday, December 18, 2019 1:55 AM