Can you use a CASE statement in a select statement...
# javascriptg
gus_dancy
07/15/2022, 11:01 PMCan you use a CASE statement in a select statement in SQL?
gus_dancy
07/15/2022, 11:03 PMI doing this: SELECT SUM(Debit - Credit) AS Tot
But if the SUM(Debit-Credit) is greater than 0 I want to subtract 20 from it. And call it Tot_Less_Fee
But if the SUM(Debit-Credit) is 0 I just want to leave it be as 0.
gus_dancy
07/15/2022, 11:11 PMI tried this:
SELECT "Tot" =
CASE
WHEN SUM(Debit-Credit) = 0 THEN 0
ELSE SUM(Debit-Credit) - 20
END
But I realize now that the case logic needs to be on the "Debit-Credit" not the SUM() of them.
d
danmurphy
07/15/2022, 11:15 PMYeah, it needs to be more like…
Copy code
SELECT SUM(CASE WHEN Debit - Credit <= 0 THEN 0
ELSE Debit - Credit - 20 END) AS stuffSum
FROM tableg
gus_dancy
07/15/2022, 11:17 PMSo I am trying to SUM(Debit-Credit - 20) but only when the Debit-Credit is greater than 0.
gus_dancy
07/15/2022, 11:20 PMOK, I just tried what you said. I think it may be working. It was returning 5655, so now when I eliminate the zero records I am getting 3915.
d
danmurphy
07/15/2022, 11:20 PMYep, that should do it then (I updated the SQL to do a “<= 0” rather than just “= 0".
g
gus_dancy
07/15/2022, 11:20 PMThat might be doing what I need. Thanks!
d
danmurphy
07/15/2022, 11:20 PMSo make sure to make that change in the first CASE statement.
g
gus_dancy
07/15/2022, 11:20 PMOh that might be good yeah.
gus_dancy
07/15/2022, 11:21 PMActually I might do <= 20.
gus_dancy
07/15/2022, 11:21 PMCan't take a 20 fee if they only paid 10.
d
danmurphy
07/15/2022, 11:21 PMSure, play with it now that you know how the syntax looks. Good luck!
g
gus_dancy
07/15/2022, 11:21 PMBut thanks for the help.
gus_dancy
07/15/2022, 11:38 PMSomething weird is happening. When getPartyFees.Tot comes out as an empty string it errors in CF. Even when I wrap it with the val() function.
gus_dancy
07/15/2022, 11:38 PMval(getPartyFees.Tot) throws an error The value '' cannot be converted to a number.
gus_dancy
07/15/2022, 11:39 PMI don't understand how the query returns an empty string. The CASE statement is inside a SUM() so shouldn't it always return aat least 0?
d
danmurphy
07/15/2022, 11:40 PMUnless you have some NULLs.
g
gus_dancy
07/15/2022, 11:40 PMNo, debit and credit are always zero or a value.
d
danmurphy
07/15/2022, 11:41 PMTry surrounding the field in a COALESCE (I think that is pretty standard across database engines?).
danmurphy
07/15/2022, 11:41 PMSomething like…
Copy code
SELECT COALESCE(SUM(CASE WHEN Debit - Credit <= 0 THEN 0
ELSE Debit - Credit - 20 END), 0) AS stuffSum
FROM tableg
gus_dancy
07/15/2022, 11:41 PMOK
d
danmurphy
07/15/2022, 11:41 PMSo, if this is all NULL, then return 0.