Re: trivial decode NOT

Mark W. Aurit (maurit_at_world.nad.northrop.com) wrote:
: Im creating a view, and need to add a new column "Acct_Desc".
: The pseudo code behind the new column is (roughly)
 

: if acct ge '120' and acct le '140' then 'Labor'
 

: Would greatly appreciate any help on how to do this in a decode
: (unless anyone knows any other ways!)
 

: Mark
: maurit_at_world.nad.northrop.com

Hi Mark, here is the DECODE to identify a column value between '150' and '160'.

Sample Table my_tab

                  
                  DEPT
                  -----
                  140
                  153
                  155
                  162

select decode(trunc((DEPT-150)/10),0,'ONEFIFTYS','other') from my_tab;

Results:

         DEPT
         ----
         other
         ONEFIFTYS
         ONEFIFTYS
         other

Explanation: To identify department numbers between 150 and 160, 
             you subtract 150 from the number, divide by 10, and 
             truncate all decimals. The result will be zero.

             For numbers outside the range, the result will be
             non-zero.

             Set up suitable ranges for the greater-than and 
             less-than tests you wish to perform in your DECODE
             using the truncate function to eliminate fractional
             parts from the answer. You can perform any conditional
             tests this way, the only way I know for DECODE's.

Best regards ....James Febbo febbo_at_njackn.com Received on Mon Apr 10 1995 - 00:00:00 CEST