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 Go to next message
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 --(Cool
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 #390011 is a reply to message #390008] Wed, 04 March 2009 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
100 posts and you don't know how to post.
Start with formatting in readable way.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: manipulate the decode function? [message #390014 is a reply to message #390008] Wed, 04 March 2009 10:29 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 #390016 is a reply to message #390014] Wed, 04 March 2009 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not happy, you should use SQL Formatter and not only use code tags.
And there is still no Oracle version.

Regards
Michel
Re: manipulate the decode function? [message #390017 is a reply to message #390015] Wed, 04 March 2009 10:56 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 #390024 is a reply to message #390020] Wed, 04 March 2009 11:46 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

Yes!
You get my dilema. I am having a hard time trying to figure out how to do a subquery in a view though.....
Re: manipulate the decode function? [message #390025 is a reply to message #390024] Wed, 04 March 2009 11:47 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Unless there's something else you're not telling us - the same way as in a normal select.
Re: manipulate the decode function? [message #390026 is a reply to message #390008] Wed, 04 March 2009 11:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: manipulate the decode function? [message #390054 is a reply to message #390025] Wed, 04 March 2009 14:45 Go to previous messageGo to next message
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 #390072 is a reply to message #390008] Wed, 04 March 2009 18:29 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why have you got table1 in the main from?
Surely it should only be in the sub-queries?
Re: manipulate the decode function? [message #390187 is a reply to message #390008] Thu, 05 March 2009 06:39 Go to previous messageGo to next message
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 Go to previous message
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(+) 

Previous Topic: oracle 10g
Next Topic: Populate a table with multiple records
Goto Forum:
  


Current Time: Sat Dec 10 06:43:15 CST 2016

Total time taken to generate the page: 0.06978 seconds