Last post Jun 24, 2020 11:40 AM by imapsp
Jun 24, 2020 06:26 AM|jsshivalik|LINK
I have below code and i want if Balance is > 0 then only that record gets executed
Select a.Code,a.Name,(Select sum(Debit)-Sum(Credit) from Test1 where Code = a.Code) as Balance from Test0 a
Jun 24, 2020 09:07 AM|XuDong Peng|LINK
Based on the query statement you provided, I created an example, I think this may be what you expect.
I made some modifications to the query statement.
create table test1(
create table test0(
insert into test0 values (1,'first'),(2,'second'),(3,'third'),(4,'fourth'),(5,'fifth')
insert into test1 values (1,12,3),(1,51,4),(1,17,16),(2,31,55),(2,77,9),(2,54,87),
Select a.Code,a.Name,(Select sum(Debit)-Sum(Credit) from Test1 where Code = a.Code) Balance
from Test0 a
Its result is:
What you want to query may be like this:
Select b.* from (
Select a.Code newCode,a.Name newName,(Select sum(Debit)-Sum(Credit) from Test1 where Code = a.Code) Balance
from Test0 a) b
where b.Balance > 0
It can be clearly seen that it only queries records with Balance greater than 0.
Hope this can help you.
Jun 24, 2020 11:40 AM|imapsp|LINK
jsshivalik, you can also try:
from Test0 a
Select sum(b.Debit) - Sum(b.Credit) as Balance
from Test1 b
where b.Code = a.Code
having sum(b.Debit) - Sum(b.Credit) > 0
Hope this help