Home » SQL & PL/SQL » SQL & PL/SQL » manipulate the decode function? (pl/sql)
| manipulate the decode function? [message #390008] |
Wed, 04 March 2009 09:42  |
deahayes3
Messages: 203
Registered: May 2006
|
Senior Member |
|
|
Hello all,
I was wondering if there is a function or a way I can manipulate the decode function to obtain the following results. For the search portion of the decode function I want it to change....
SELECT
DECODE(inc.rc_id1, root.rc_id, root.description) cause1,
inc.rc_id, --(5)
DECODE(inc.rc_id2, root.rc_id, root.description) cause2,
inc.rc_id2, --(6)
DECODE(inc.rc_id3, root.rc_id, root.description) cause3,
inc.rc_id3, --(7)
DECODE(inc.rc_id4, root.rc_id, root.desctiption) cause4,
inc.rc_id4 --(
from TABLE1 root, TABLE2 inc
The problem I am having is the value for root.rc_id never mathes cause 2, 3, or 4, it always compares it with value from cause1 (e.g = 5).
Any ideas?
|
|
|
|
|
|
| Re: manipulate the decode function? [message #390014 is a reply to message #390008] |
Wed, 04 March 2009 10:29  |
deahayes3
Messages: 203
Registered: May 2006
|
Senior Member |
|
|
Happy now.....
Hello all,
I was wondering if there is a function or a way I can manipulate the decode function to obtain the following results. For the search portion of the decode function I want it to change....
SELECT
DECODE(inc.rc_id1, root.rc_id, root.description) cause1,
inc.rc_id, --(5)
DECODE(inc.rc_id2, root.rc_id, root.description) cause2,
inc.rc_id2, --(6)
DECODE(inc.rc_id3, root.rc_id, root.description) cause3,
inc.rc_id3, --(7)
DECODE(inc.rc_id4, root.rc_id, root.desctiption) cause4,
inc.rc_id4 --(10)
from TABLE1 root, TABLE2 inc
The problem I am having is the value for root.rc_id never mathes cause 2, 3, or 4, it always compares it with value from cause1 (e.g = 5).
Any ideas?
|
|
|
|
| Re: manipulate the decode function? [message #390015 is a reply to message #390008] |
Wed, 04 March 2009 10:37 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Well it's easier to read but it still makes no sense.
| Quote: |
I was wondering if there is a function or a way I can manipulate the decode function to obtain the following results
|
What results? I see some code that presumably doesn't work for you but no results.
You need to give us a test case - create table statements, inserts and the expected result.
|
|
|
|
|
|
| Re: manipulate the decode function? [message #390017 is a reply to message #390015] |
Wed, 04 March 2009 10:56 |
deahayes3
Messages: 203
Registered: May 2006
|
Senior Member |
|
|
I am asking if a function exist that I can use besides DECODE to retrieve my desired results or is there a way I can use DECODE.
The select statement is actually a view. I am trying to pull the description from table1 (root) that is matched by the id number(rc_id#) of table 2 (inc).
SELECT
DECODE(inc.rc_id1, root.rc_id, root.description) cause1,
inc.rc_id, --(5 = 5 therefore result is 'Exposed to sunlight')
DECODE(inc.rc_id2, root.rc_id, root.description) cause2,
inc.rc_id2, --(6 = 5 therefore result is null)
DECODE(inc.rc_id3, root.rc_id, root.description) cause3,
inc.rc_id3, --(7 = 5 therefore result is null)
DECODE(inc.rc_id4, root.rc_id, root.desctiption) cause4,
inc.rc_id4 --(10 = 5 therefore result is null)
from TABLE1 root, TABLE2 inc
here is sample code for table1 root
rc_id description
----------------------------------
5 EXPOSED TO SUNLIGHT
6 CHEMICAL BURN
7 ABRASION
10 OTHER
here is sample code for table2 inc
pk_id rc_id1 rc_id2 rc_id3 rc_id4
-------------------------------------
1 5 6
2 6 10 5
3 7 5 6 10 |
|
|
|
| Re: manipulate the decode function? [message #390020 is a reply to message #390008] |
Wed, 04 March 2009 11:06 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
So root is a lookup table?
You've a conceptual problem here.
What you need to is access root once per id you're looking up a value for.
Functions aren't the issue - you're query needs to access root multiple times, either as sub-querys or multiple times in the from clause.
Only function you can use is one you write yourself to select the description from root.
EDIT: typo
[Updated on: Wed, 04 March 2009 11:08] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: manipulate the decode function? [message #390054 is a reply to message #390025] |
Wed, 04 March 2009 14:45 |
deahayes3
Messages: 203
Registered: May 2006
|
Senior Member |
|
|
No success, I can not put the condition in the where clause because sometimes 'inc.rc_id2' is null therefore I am not getting the result I was looking for. I tried the following in my view but still not result....
SELECT
DECODE(inc.rc_id1, root.rc_id, root.description) cause1,
inc.rc_id,
root.root_description IN (SELECT ROOT_DESCRIPTION FROM TABLE1 WHERE rc_id = inc.rc_id2)
inc.rc_id2,
DECODE(inc.rc_id3, root.rc_id, root.description) cause3,
inc.rc_id3,
DECODE(inc.rc_id4, root.rc_id, root.desctiption) cause4,
inc.rc_id4
from TABLE1 root, TABLE2 inc
|
|
|
|
|
|
| Re: manipulate the decode function? [message #390187 is a reply to message #390008] |
Thu, 05 March 2009 06:39 |
c_stenersen
Messages: 255
Registered: August 2007
|
Senior Member |
|
|
You'll still get a hit even with null values if you do an outer join.
SELECT
R1.description cause1,
R2.description cause2,
R3.description cause3,
R4.description cause4
FROM
table2 I, table1 R1, table1 R2, table1 R3, table1 R4
WHERE I.rc_id1 = R1.rc_id(+)
AND I.rc_id2 = R2.rc_id(+)
AND I.rc_id3 = R3.rc_id(+)
AND I.rc_id4 = R4.rc_id(+) |
|
|
|
| Re: manipulate the decode function? [message #390223 is a reply to message #390187] |
Thu, 05 March 2009 09:04 |
deahayes3
Messages: 203
Registered: May 2006
|
Senior Member |
|
|
Thank you, Thank you, thank you. It worked. Once I added table1 3 more times and removed the Decode function, it works. Thanks.
SELECT
DECODE(inc.rc_id1, root.rc_id, root.description) cause1,
inc.rc_id,
root2.description cause2,
inc.rc_id2,
root3.description)cause3,
inc.rc_id3,
root4.desctiption) cause4,
inc.rc_id4
from TABLE1 root1, TABLE1 root2, TABLE1 root3, TABLE1 root4, TABLE2 inc
where
inc.rc_id = root1.rc_id(+)
AND Inc.rc_id2 = Root2.rc_id(+)
AND Inc.rc_id3 = Root3.rc_id(+)
AND Inc.rc_id4 = Root4.rc_id(+)
|
|
|
|
Goto Forum:
Current Time: Thu Dec 12 07:37:38 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
