If condition in SQL 1

[TStriker]

Hi All,

Rookie question here I think. Here is the basic SQL:

SELECT
seg.acct_per,
seg.acct_yr,
seg.base_amt_debit - seg.base_amt_credit NET_TOTAL,
seg.cost_cntr_no,
seg.gl_acct_no
FROM
seg_acct_per_ttl seg
WHERE
seg.seg_no = 3 and
seg.data_type_cd = 'ACTUAL';

I would like to have the NET_TOTAL be multiplied by a negative 1 (-1) only of the seg.gl_acct_no is in the 40000 range. It must be possible but I'm stumbling a lot. Can somebody lend a hand?

Thanks!

-Striker

 

[SantaMufasa]

Striker,

Actually, it's a bit tricky. Try this:

SELECT 
  seg.acct_per,
  seg.acct_yr,
  decode(sign(40000-seg.gl_acct_no)*sign(49999-seg.gl_acct_no)
        ,1,seg.base_amt_debit - seg.base_amt_credit
        ,-1*(seg.base_amt_debit - seg.base_amt_credit)
        ) NET_TOTAL,
seg.base_amt_debit - seg.base_amt_credit NET_TOTAL,
  seg.cost_cntr_no,
  seg.gl_acct_no
FROM 
  seg_acct_per_ttl seg
WHERE 
  seg.seg_no = 3 and
  seg.data_type_cd = 'ACTUAL';

The guts (i.e., DECODE function) of the above code mean:

1) Subtract seg.gl_acct_no from 40000 and return, as the result of the SIGN function,
a) a "-1" if the subtraction is negative
b) a "0" if the subtraction is zero
c) a "1" if the subraction is positive.

2) Do the same calculation against 49999.

3) Multiply the two SIGN results.
a) If seg.gl_acct_no is less than 40000, then the product is (1 * 1) = 1.
b) If seg.gl_acct_no is 40000, then the product is (0 * 1) = 0.
c) If seg.gl_acct_no is > 40000 but less than 49999, then the product is (-1 * 1) = -1
d) If seg.gl_acct_no is 49999, then the product is (-1 * 0) = 0.
e) If seg.gl_acct_no is > 49999, then the product is (-1 * -1) = 1.

4) If the product = 1 (i.e, outside the 40000 range), then do a straight subtraction; otherwise, multiply the subtraction result by -1.

Let us know if this resolves satisfactorily your issue.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[TStriker]

Mufasa,

It seems to work! I had been working at it using a UNION method but yours seems more clean.

Thanks!

-Striker

 

[SantaMufasa]

Yes, Striker, UNION would work, as well, but I agree that the code, above, is probably faster than the UNION.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

Nice job on the decode Dave.

If you have a newer database (9i and above) you can also use the case statement

SELECT
  seg.acct_per,
  seg.acct_yr,
  case 
  when seg.gl_acct_no between 40000 and 40999 then
   (seg.base_amt_debit - seg.base_amt_credit) * -1 
  else
   seg.base_amt_debit - seg.base_amt_credit
   end NET_TOTAL,
  seg.cost_cntr_no,
  seg.gl_acct_no
FROM
  seg_acct_per_ttl seg
WHERE
  seg.seg_no = 3 and
  seg.data_type_cd = 'ACTUAL';

Bill
Oracle DBA/Developer
New York State, USA