Home » SQL & PL/SQL » SQL & PL/SQL » nesting DECODE functions
nesting DECODE functions [message #17204] Wed, 29 December 2004 12:12 Go to next message
Matthew Waugh
Messages: 71
Registered: October 2004
Member
Will this work? I don't have access to an Oracle server right now, so I can't test it.

DECODE(
JOB.EMPL_TYPE,
'H',
DECODE(GREATEST(JOB.ANNUAL_RT,35000),35000,4.5,4.0),
3.0)

Do I need a set of parentheses around the nested DECODE?
Re: nesting DECODE functions [message #17207 is a reply to message #17204] Wed, 29 December 2004 13:54 Go to previous message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
You have not given a complete example, so I will address some hypothetical situations. In general, you will not need an extra set of parentheses around the nested decode. What you have provided should return results from within a SQL select statement, but may not be usable within PL/SQL as "variable := DECODE .....". Left as it is, it will return a lengthy header with a wide column. You could add an alias and some formatting. Although the query will return results, the results may or may not be what you want. If the job.annual_rt can ever have a null value, then the greatest function will not return 35000 and your result will be 4.0. If, when job.annual_rt is null, you want to return 4.5, then you can add a nested nvl function. I have put those differences in bold in the example below. Please see the demonstration below, in which I have used a sampling of data that tests various possibilities and illustrates the differences.

test data:
scott@ORA92> SELECT empl_type, annual_rt FROM job
  2  /

E  ANNUAL_RT
- ----------
H      35000
H      34000
H      36000
A      35000
B      34000
C      36000

H
D

9 rows selected.


queries:
scott@ORA92> SELECT DECODE (job.empl_type,
  2  		    'H', DECODE (GREATEST (job.annual_rt, 35000),
  3  				 35000, 4.5,
  4  					4.0),
  5  			 3.0)
  6  FROM   job
  7  /

DECODE(JOB.EMPL_TYPE,'H',DECODE(GREATEST(JOB.ANNUAL_RT,35000),35000,4.5,4.0),3.0)
---------------------------------------------------------------------------------
                                                                              4.5
                                                                              4.5
                                                                                4
                                                                                3
                                                                                3
                                                                                3
                                                                                3
                                                                                4
                                                                                3

9 rows selected.


scott@ORA92> COLUMN whatever FORMAT 9.9
scott@ORA92> SELECT empl_type,
  2  	    annual_rt,
  3  	    DECODE (job.empl_type,
  4  		    'H', DECODE (GREATEST (job.annual_rt, 35000),
  5  				 35000, 4.5,
  6  					4.0),
  7  			 3.0) AS whatever
  8  FROM   job
  9  /

E  ANNUAL_RT WHATEVER
- ---------- --------
H      35000      4.5
H      34000      4.5
H      36000      4.0
A      35000      3.0
B      34000      3.0
C      36000      3.0
                  3.0
<b>H                 4.0</b>
D                 3.0

9 rows selected.


scott@ORA92> SELECT empl_type,
  2  	    annual_rt,
  3  	    DECODE (job.empl_type,
  4  		    'H', DECODE (GREATEST (NVL (job.annual_rt, 0), 35000),
  5  				 35000, 4.5,
  6  					4.0),
  7  			 3.0) AS whatever
  8  FROM   job
  9  /

E  ANNUAL_RT WHATEVER
- ---------- --------
H      35000      4.5
H      34000      4.5
H      36000      4.0
A      35000      3.0
B      34000      3.0
C      36000      3.0
                  3.0
<b>H                 4.5</b>
D                 3.0

9 rows selected.

scott@ORA92> 
Previous Topic: What is this operator doing?
Next Topic: Insert and Selectin same SP
Goto Forum:
  


Current Time: Thu Jul 24 21:02:09 CDT 2025