CODEWITHSUNDEEP
sqlcase-when
user9848707
user9848707

Reputation:

SQL: CASE WHEN not working

I am trying to return either "Increased adjustment" or "Decreased adjustment" depending on the value in the "amount" column being positive or negative.

CASE amount
when amount > 0 then "Increase Adjustment"
when amount < 0 then "Decreased Adjustment"
else "ERROR"
end as 
transaction_type

Any advise would be greatly appreciated...

Edit: Entire select=:

select
tx_date,
bank_account,
description,
amount,
currency,
CASE bank_account
when 'CAD-FTX' then 'Suspense_CAD'
when 'USD-PRO' then 'Suspense_USD'
when 'CAD-PRO' then 'Suspense_CAD'
when 'USD-ALL' then 'Suspense_USD'
when 'TD-USA' then 'Suspense_USD'
end
suspense_account,
CASE amount
when cast(amount as float) > 0 then "Increase Adjustment"
when cast(amount as float) < 0 then "Decreased Adjustment"
end 
transaction_type
from [sys_tx_combined]

Upvotes: 0

Views: 11278

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Here is a simpler way to write your expressions:

select tx_date, bank_account, description, amount, currency,
       (case when bank_account in ('CAD-FTX', 'CAD-PRO')
             then 'Suspense_CAD'
             when bank_account in ('USD-PRO', 'USD-ALL', 'TD-USA')
             then 'Suspense_USD'
         end) suspense_account,
       (case when amount > 0 then 'Increase Adjustment'
             when amount < 0 then 'Decreased Adjustment'
        end) as transaction_type
from [sys_tx_combined];

Notes:

  • When working with case expressions for such mapping, I prefer to have one condition for each output condition, rather than one condition for each input.
  • The cast(amount as float) almost certainly is not necessary, so I removed it.
  • Use singe quotes for string constants, even if your database supports double quotes (those should be used for other purposes).

Upvotes: 0

Daniel Marcus
Daniel Marcus

Reputation: 2686

select tx_date, bank_account, description, amount, currency, 
CASE 
    when bank_account ='CAD-FTX' then 'Suspense_CAD' 
    when bank_account = 'USD-PRO' then 'Suspense_USD' 
    when bank_account = 'CAD-PRO' then 'Suspense_CAD' 
    when bank_account = 'USD-ALL' then 'Suspense_USD' 
    when bank_account = 'TD-USA' then 'Suspense_USD' 
end suspense_account, 
CASE 
    when cast(amount as float) > 0 then 'Increase Adjustment' 
    when cast(amount as float) < 0 then 'Decreased Adjustment' 
end 
transaction_type 
from [sys_tx_combined]

Upvotes: 1

DrHouseofSQL
DrHouseofSQL

Reputation: 548

CASE
when amount > '0' then "Increased Adjustment"
when amount < '0' then "Decreased Adjustment"
else "ERROR"
end as 
transaction_type

Upvotes: 0

mattinbits
mattinbits

Reputation: 10428

Because you start your statement with

CASE amount

You're specifying a simple CASE expression over amount, meaning the WHEN clauses can only be equality checks. Try a searched expression:

CASE
when amount > 0 then "Increase Adjustment"
when amount < 0 then "Decreased Adjustment"
else "ERROR"
end as 
transaction_type

More info: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017

Upvotes: 0

The Impaler
The Impaler

Reputation: 48769

How about:

CASE
when amount > 0 then "Increase Adjustment"
when amount < 0 then "Decreased Adjustment"
else "ERROR"
end as 
transaction_type

Upvotes: 0

Related Questions