locked
Error - Column 'OACT.FatherNum' 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

      IN below query i am getting above error

    SELECT T1.Account,Max(T0.AcctName),(Select AcctName from Oact where AcctCode = T0.FatherNum)
    ,Isnull((SELECT SUM(T3.Debit - T3.Credit) FROM OJDT T2
    INNER JOIN JDT1 T3 ON T2.TransId = T3.TransId
    WHERE T3.Account = T1.Account 
    GROUP BY T3.Account),0) 'Balance'
    FROM OACT T0
    INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account
    GROUP BY T1.Account
    Having SUM(T1.Debit - T1.Credit) != 0
    ORDER BY T1.Account

    Thanks

    Saturday, December 7, 2019 10:47 AM

All replies

  • User-719153870 posted

    Hi jsshivalik,

    You code Select AcctName from Oact where AcctCode = T0.FatherNum might select multiple records which will conflict with your GROUP BY T1.Account, you can see the problem logically.

    A very simple way that i can provide since we don't know the relationship between your tables is select only one record from your OACT.

    Please refer to below code:

    SELECT T1.Account,Max(T0.AcctName),(Select max(AcctName) from Oact where AcctCode = T0.FatherNum)
    ,Isnull((SELECT SUM(T3.Debit - T3.Credit) FROM OJDT T2
    INNER JOIN JDT1 T3 ON T2.TransId = T3.TransId
    WHERE T3.Account = T1.Account 
    GROUP BY T3.Account),0) 'Balance'
    FROM OACT T0
    INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account
    GROUP BY T1.Account
    Having SUM(T1.Debit - T1.Credit) != 0
    ORDER BY T1.Account

    Best Regard,

    Yang Shen

    Monday, December 9, 2019 3:26 AM
  • User-797751191 posted

    Hi

      I have below code but getting same problem

    Oact.FatherNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Select T0.Account , (Select Max(T.AcctName) from Oact T where T.AcctCode = T1.FatherNum) as Heading 
    from Jdt1 T0
    inner join Oact T1 
    on T0.Account = T1.AcctCode group by T0.Account

    Thanks

    Tuesday, December 10, 2019 12:44 PM
  • User-719153870 posted

    Hi jsshivalik,

    The alias T and T1 are both for the same table Oact in your query which is little strange.

    In this case, where T.AcctCode = T1.FatherNum will make it confusing to match your rows between T and T1.

    I suggest you can modify your query like below which is just assumptions based on the code you give since we don't know the table structure, data and relationship for now:

    select T0.Account,max(T1.AcctName) as Heading  from Jdt1 T0 inner join Oact T1 on T0.Account=T1.AcctCode where T1.FatherNum=T1.AcctCode group by T0.Account

    If this is not what you want, maybe you could provide the table structure, sample data, relationships between them and what you expected to get as result.

    Below is the demo i make according to your query:

    create table #Jdt1
    (
    Account int
    )
    
    create table #Oact
    (
    AcctCode int,
    AcctName varchar(50),
    FatherNum int
    )
    
    insert into #Jdt1 values(1),(2),(3)
    insert into #Oact values(1,'a',1),(2,'b',4),(3,'c',3)
    
    select * from #Jdt1
    select * from #Oact
    
    --Select T0.Account , (Select Max(T.AcctName) from #Oact T where T.AcctCode = T.FatherNum) as Heading 
    --from #Jdt1 T0
    --inner join #Oact T1 
    --on T0.Account = T1.AcctCode group by T0.Account
    
    --select * from #Jdt1 T0 inner join #Oact T1 on T0.Account=T1.AcctCode
    
    select T0.Account,max(T1.AcctName) as Heading  from #Jdt1 T0 inner join #Oact T1 on T0.Account=T1.AcctCode where T1.FatherNum=T1.AcctCode group by T0.Account

    And its result:

    Best Regard,

    Yang Shen

    Wednesday, December 11, 2019 1:23 AM