0

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]
Improve this question
2
  • Your sign is the same, change the applicable one to < and rerun Commented May 25, 2018 at 20:07
  • Also don't forget the 'D' at the end of the word 'Increase' Commented May 25, 2018 at 20:10

5 Answers 5

Reset to default
1 
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]
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Still the same error... Thank you for the response though.
Can you post the entire select statement please?
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]
One thing, change the double quotes to single quotes surrounding the increase and decrease adjustment
Try updated code above - also what kind of field is 'amount'?
|
0

How about:

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

3 Comments

Damn didn't even notice, thanks for pointing that out but I'm still getting the same error. "operator does not exist: numeric = boolean"
Please add a whole SQL statement. Maybe the error is somewhere else.
Just added it sorry about that. "operator does not exist: numeric = boolean"
0

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

Improve this answer

1 Comment

Thank you for the response! The same sign was just a typo, i fixed it for future reference.
0 
CASE
when amount > '0' then "Increased Adjustment"
when amount < '0' then "Decreased Adjustment"
else "ERROR"
end as 
transaction_type
Improve this answer

2 Comments

Don't think the amount column is varchar.
Is there a way to make it varchar in the query?
0

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).
Improve this answer

3 Comments

Thank you for the response! Really appreciate the help. Amount is an numeric value, 2 decimal places and negative & positive values. Thank you again for the response!
Why would you cast it to a float for a comparison?
Was just following someone's advice from a different comment. I don't know how to change the value to make it work

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.