Home » SQL & PL/SQL » SQL & PL/SQL » nesting DECODE functions
| nesting DECODE functions [message #17204] |
Wed, 29 December 2004 12:12  |
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  |
 |
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>
|
|
|
|
Goto Forum:
Current Time: Thu Jul 24 21:02:09 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
