Oact.GroupMask' 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 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

Answer 1

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

Answer 2

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