CODEWITHSUNDEEP
sqlcase
Ren Cantu
Ren Cantu

Reputation: 67

Case Negative Column

I have this table

ID TYPE AMOUNT
1 - 1234
2 + 4567

I want column amount to be displayed like this

ID TYPE AMOUNT
1 - -1234
2 + 4567

I tried

SELECT
    a.* , 
    CASE 
        WHEN a.type = '-' 
            THEN a.AMOUNT * -1 
            ELSE 'null' 
    END
FROM
    dual a

but it's not working because data format.

Can someone help me?

Thanks!

Upvotes: 0

Views: 31

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

Just another option sign()

Example

Declare @YourTable Table ([ID] int,[TYPE] varchar(50),[AMOUNT] int)  
Insert Into @YourTable Values 
 (1,'-',1234)
,(2,'+',456)
 
Select * 
      ,NewValue = sign(Type+'1')*Amount
 from @YourTable

Results

ID  TYPE    AMOUNT  NewValue
1   -       1234    -1234
2   +       456     456

After thought...

You could also use try_convert()

try_convert(int,concat(Type,Amount))  -- use desired datatype

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270061

You would seem to want:

(case when a.type = '-' then - a.amount else a.amount end)

Upvotes: 0

Related Questions