locked
Inner Select Statement gibing wrong result RRS feed

  • Question

  • User-797751191 posted

    Hi

      I have below cod but when i write this statement then it shows Balance = 0 in all records

    (Select isnull(Sum(A2.Debit) - Sum(A2.Credit),0) from jdt1 A2 inner join ojdt a3 on A2.transid = A3.transid
    where a2.Account = T1.Account
    and a2.RefDate = '2019/03/31' )

    Select T1.Account,(Select Sum(A0.Debit) - Sum(A0.Credit) from jdt1 A0 inner join ojdt a1 on A0.transid = A1.transid
    where a0.Account = T1.Account and a0.RefDate <= '2019/03/31'
    ) - (Select isnull(Sum(A2.Debit) - Sum(A2.Credit),0) from jdt1 A2 inner join ojdt a3 on A2.transid = A3.transid
    where a2.Account = T1.Account 
    and a2.RefDate = '2019/03/31' ) 
     as Balance
    			from Oact T0
    			inner join JDT1 T1 on T0.AcctCode = T1.Account
    			inner join OJDT T2 on T1.TransId = T2.TransId
    			where  And T2.RefDate <= '2019/03/31'
    			group by T1.Account

    Thanks

    Monday, March 23, 2020 5:20 PM

All replies

  • User288213138 posted

    Hi jsshivalik,

    Select T1.Account,(Select Sum(A0.Debit) - Sum(A0.Credit) from jdt1 A0 inner join ojdt a1 on A0.transid = A1.transid
    where a0.Account = T1.Account and a0.RefDate <= '2019/03/31'
    ) - (Select isnull(Sum(A2.Debit) - Sum(A2.Credit),0) from jdt1 A2 inner join ojdt a3 on A2.transid = A3.transid
    where a2.Account = T1.Account 
    and a2.RefDate = '2019/03/31' ) 
     as Balance
    			from Oact T0
    			inner join JDT1 T1 on T0.AcctCode = T1.Account
    			inner join OJDT T2 on T1.TransId = T2.TransId
    			where  And T2.RefDate <= '2019/03/31'
    			group by T1.Account

    Based on the code you posted, I cannot reproduce your problem.

    Please provide me the structure and sample data of all your tables.

    Best regards,

    Sam

    Tuesday, March 24, 2020 2:48 AM