SQL with Sum & Case

[Henry Olive 5]

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

 

[Virgo 18]

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

 

[Virgo 18]

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

[Henry Olive 5]

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.