Home » RDBMS Server » Server Administration » Using AND & OR within DECODE statements
| Using AND & OR within DECODE statements [message #374857] |
Fri, 06 July 2001 14:49  |
Bruce Weinstein
Messages: 4
Registered: May 2001
|
Junior Member |
|
|
Hi,
Here is my problem.
Following is what I would like to convert into DECODE statement.
if (JOB.action = 'HIR' or JOB.action = 'REH') and (JOB.action_reason = 'APT' or JOB.action_reason = 'APS')
then a = 1 ;
else a = 0 ;
I want to make the whole statement return a 1 or 0 as I will be summing the values to give me count. So I imagine I will have to wrap statement with SUM function too. It is mainly the DECODE I am having trouble with.
How could I achieve this in ORACLE. I am assuming it would have to be done using the DECODE, that is why I mentioned in subject.
Your help would be appreciated as it is kind of vital for completion of my program.
Thanks in advance.
...Bruce
|
|
|
|
| Re: Using AND & OR within DECODE statements [message #374859 is a reply to message #374857] |
Sat, 07 July 2001 10:45  |
Muhamad Sirajdin
Messages: 12
Registered: May 2001
|
Junior Member |
|
|
SELECT SUM(COUNT(TESTTAB.NAME)) FROM TESTTAB GROUP BY TESTTAB.NAME,
TESTTAB.ACTION HAVING
DECODE(TESTTAB.NAME,'APT',1,'REF',1,'APS',1,0)=1 AND
DECODE(TESTTAB.ACTION,'HIR',0,'OHP',0,'OPP',0,1)=0
/
hth
|
|
|
|
|
|
| Re: Using AND & OR within DECODE statements [message #374871 is a reply to message #374857] |
Mon, 09 July 2001 07:35 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
As Palanikarupaiyan said, the best way to do this is to create a stored function and call that wherever you want the decode.
If you are determined to have a decode, try this one.
I refer to the fields as ACT and REA to save typing:
DECODE(rea
,'APT',DECODE(act,'HIR',1,'REH',1,0)
,'APS',DECODE(act,'HIR',1,'REH',1,0)
,0)
|
|
|
|
| Re: Using AND & OR within DECODE statements [message #374889 is a reply to message #374857] |
Tue, 10 July 2001 00:34 |
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
|
Member |
|
|
I guess you could use the same function, DECODE, for whole condition check within the same SELECT statement.
SELECT DECODE(
DECODE(JOB.ACTION,'HIR',1,'REH',1,2),
DECODE(JOB.ACTION_REASON,'APT',1,'APS',1,3),1,0) FROM table_name;
Explanation:
The first inner DECODE for JOB.ACTION returns the same result 1 for its contents HIR and REH, otherwise 2
Similarily the second inner DECODE for JOB.ACTION_REASON also returns same result 1 for its contents APT and APS, otherwise 3.
Finally the outer and main DECODE compares the results of two inner DECODEs 1 and 1 then results 1 otherwise 0 as the final output for that row.
Here, in place of a column/filed in DECODE, a DECODE condition is given to represent a value 1, and in the place of a literal or conditional value another DECODE is given that returns 1 or 0. If both the represented values are same (1 = 1), then the final result is 1 or otherwise 0.
The false values 2 and 3 are given to differ with each other when the condition is not met.
Hope you understand and it resolves your problem.
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 20:00:29 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
