Call Decode Result In a Function

[getjbb]

I am during a select with a couple of decodes included. How can I use the result (tr.trans_type) from the first decode in function call (SUBSTANCES.get_desc) in the second decode statement.

SELECT TRANS_NUM,
sub.ACCOUNT,
TRANS_DATE,
DECODE( (trans_num is null AND tr.trans_type=6 AND item_code='C'), THEN 19
ELSE tr.trans_type
END) tr.trans_type,
DECODE (
show_first_desc,
'Y', description
|| ' '
|| SUBSTANCES.get_desc (trans_num, tr.trans_type),
DECODE (show_second, 'Y', description, NULL))
description
from transactions tr, substance sub
where transactions.account = substance.account;

getjbb

 

[SantaMufasa]

Getjbb,

There were a couple of syntax issues in your original code, and I've aligned the corrected code so that you are able to read it a bit better.

Regarding your question about how to refer to the results of the original DECODE: simply re-state that DECODE where you need it (as I have done, below):

SELECT TRANS_NUM
      ,sub.ACCOUNT
      ,TRANS_DATE
      ,DECODE(trans_num||tr.trans_type||item_code
             ,'6C',19
             ,tr.trans_type) trans_type
      ,DECODE (show_first_desc
              ,'Y',description||'  '||
                   SUBSTANCES.get_desc(trans_num
                                      ,DECODE(trans_num||tr.trans_type||item_code
                                             ,'6C',19
                                             ,tr.trans_type
                                             )
                                      )
              ,DECODE (show_second
                      ,'Y',description
                      ,NULL
                      )
              ) description
  from transactions tr, substance sub
 where tr.account = sub.account;

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[getjbb]

SantaMufasa,

Thank you. This is what I needed.

getjbb