Inner Select Statement gibing wrong resultRSS

• • Reply
  jsshivalik

  Member

  130 Points

  481 Posts

  Inner Select Statement gibing wrong result

  Mar 23, 2020 05:20 PM|jsshivalik|LINK

  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

• • Reply
  samwu

  Contributor

  2880 Points

  1203 Posts

  Re: Inner Select Statement gibing wrong result

  Mar 24, 2020 02:48 AM|samwu|LINK

  Hi jsshivalik,

  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

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.