locked
Query RRS feed

  • Question

  • User-797751191 posted

    Hi

      I have below query and i want it should return only those Accounts from T0 which have Account in Jdt1 and location in JDT2 = 1

    select T0.AcctCode,T0.AcctName , (Select Sum(T1.Debit) - Sum(T1.Credit) from JDT1 T1 inner join OJDT T2 on T1.TransId = T2.TransId

    where T1.Account = T0.AcctCode ) from Oact T0

    Thanks

    Saturday, December 7, 2019 8:26 AM

Answers

  • User452040443 posted

    Hi,

    Try:

    with CTE_Sum as
    (
        Select T1.Account, Sum(T1.Debit) - Sum(T1.Credit) as MySum
        from JDT1 T1 
        inner join OJDT T2 on T1.TransId = T2.TransId
        group by T1.Account
    )
    
    select T0.AcctCode, T0.AcctName, C.MySum
    from Oact T0
    inner join CTE_Sum C on C.Account = T0.AcctCode

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 7, 2019 1:04 PM