Not being able to reverse the sign in a Numeric column

[Vara]

Based on the condition of 'Deposit' and category 'OUTFLOWS', i want to reverse the sign in CHECKINGACCOUNT_V1  for  transamount to be negative otherwise keep it positive   However when I execute the query it does nothing to the sign. It is as though it is ignoring the CASE statement.  See SQL below   (SQLtests OK but results not what I expected)

Code: Select all

select transcode,categid, subcategid
, CASE categid  WHEN '17' and transcode = 'Deposit' then  -1*transamount  else transamount end as actuals 
from checkingaccount_v1 
where subcategid not in ('-1', '98','99');

See results spreadsheet attached. The shaded area was expected to have negative amounts but that was not the case!

I would be grateful if anybody can guide me on how to get this running properly.

Thanks
Vara

[Nikolay]

Code: Select all

select t.transcode, t.categid, t.subcategid
, case t.SUBCATEGID when -1 then c.CATEGNAME else c.CATEGNAME||':'|| s.SUBCATEGNAME end as Category
, CASE t.TRANSCODE  WHEN 'Deposit' then  t.transamount  else -t.transamount end as actuals 
from checkingaccount_v1 t 
inner join CATEGORY_V1 c on t.CATEGID=c.CATEGID
left join SUBCATEGORY_V1 s on t.SUBCATEGID=s.SUBCATEGID and s.CATEGID=c.CATEGID
where t.subcategid not in ('-1', '98','99');

[Nikolay]

The same query but with 'WITH' usage

Code: Select all

with c AS (select c.CATEGID, s.SUBCATEGID
        , c.CATEGNAME||':'||s.SUBCATEGNAME CATEGORY
    from CATEGORY_V1 c, SUBCATEGORY_V1 s 
    where c.CATEGID = s.CATEGID
    union all
    select CATEGID, -1 SUBCATEGID, CATEGNAME  from CATEGORY_V1)
select t.transcode, t.categid, t.subcategid
, CASE t.TRANSCODE  WHEN 'Deposit' then  t.transamount  else -t.transamount end as actuals 
, c.CATEGORY
from checkingaccount_v1 t
inner join c on c.CATEGID = t.CATEGID and t.SUBCATEGID=c.SUBCATEGID
--where c.CATEGORY = 'Transfer'

[Nikolay]

Just created sql script to get all transactions included split.

Code: Select all

with c AS (select c.CATEGID, s.SUBCATEGID
        , c.CATEGNAME||':'||s.SUBCATEGNAME CATEGORY
    from CATEGORY_V1 c, SUBCATEGORY_V1 s 
    where c.CATEGID = s.CATEGID
    union all
    select CATEGID, -1 SUBCATEGID, CATEGNAME  from CATEGORY_V1),
    
    t as (select t.TRANSDATE , t.transcode, t.PAYEEID
        , case ifnull(t.categid, -1) when -1 then s.categid else t.categid end categid
        , case when ifnull(t.categid, -1) = -1 AND ifnull(t.subcategid, -1) = -1 then s.subcategid else t.subcategid end subcategid
        ,  (case ifnull(s.splittransid, -1) when -1 then t.transamount else s.splittransamount end)
            *(case t.transcode when 'Deposit' then 1 else -1 end) as Amount
        from checkingaccount_v1 t
            LEFT JOIN splittransactions_v1 s ON s.transid=t.transid )
select  t.TRANSDATE, c.CATEGORY, t.AMOUNT
from t
inner join c on c.CATEGID = t.CATEGID and t.SUBCATEGID=c.SUBCATEGID

[Vara]

Thanks alot