Jump to content
Sign in to follow this  
Henry Olive

SQL with Sum & Case

Recommended Posts

I wish everyone a healthy day.

Interbase 2007
 

SELECT  C.CUSTNO, C.CUSTNAME, SUM (CM.DEBIT-CM.CREDIT)BALANCE,

        Case When CAST(SUM (CM.DEBIT-CM.CREDIT) AS NUMERIC(15,2)) > 0 then
         'Debit' else 'Credit' end as STATE
FROM CUSTMOVEMENTS CM /* it is a View */
LEFT JOIN CUSTOMER C ON CM.CUSTNO=C.CUSTNO
WHERE  CM.TDATE <= '03/30/2021'
GROUP BY C.CUSTNO, C.CUSTNAME
HAVING SUM (CM.DEBIT-CM.CREDIT) <> 0


In the Results, BALANCES are correct but some State is wrong
CustNo...CustName........Balance.....State

1.................AAA.......................1000.......Debit (Correct)

2.................BBB....................... 2000.......Credit (should have been Debit) 

3.................CCC..................... -3000.......Debit (should have been Credit)

 

I had to use CAST in above sql because if i dont use it i get 

Bad BLR  Invalid Stream error msg.

What am i doing wrong ?

Thank You

 

Share this post


Link to post

Try:

 

SELECT X.*, Case When CAST(X.BALANCE  AS NUMERIC(15, 2) > 0 then 'Debit' else 'Credit' as STATE

FROM

(

SELECT  C.CUSTNO, C.CUSTNAME, SUM (CM.DEBIT-CM.CREDIT)BALANCE

FROM CUSTMOVEMENTS CM /* it is a View */
LEFT JOIN CUSTOMER C ON CM.CUSTNO=C.CUSTNO
WHERE  CM.TDATE <= '03/30/2021'
GROUP BY C.CUSTNO, C.CUSTNAME
HAVING SUM (CM.DEBIT-CM.CREDIT) <> 0

) X

 

Share this post


Link to post

Also, I hope you do not have NULL values in DEBIT and CREDIT fields at CUSTMOVEMENTS  table.

Share this post


Link to post

Thank you so much Virgo.
 

IB-2007 doesnt have CTE & Derived table 

I solved my problem in Delphi side, adding a Calc Field into Query result.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×