Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

parent/child transform query (oracle 10g)

Posted on 2011-02-16
10
Medium Priority
?
843 Views
Last Modified: 2012-06-27
I'd trying to write a SQL script (without using DML) to change the way data is presented for a report.  

I am trying to take data that looks like this:


TreeID      Node      Question                                     Result      NextNode            EndPoint
1              1              Is it blue?                                     YES               2                     {null}
1              1              Is it blue?                                      NO              {null}              You lose.
1              2              Is bigger than a breadbox?      YES               3                      {null}
1              2              Is bigger than a breadbox?      NO               {null}             You lose.
1              3              Is it a car?                             YES               {null}             You win.
1              3              Is it a car?                              NO              {null}             You lose.


To look like this:
Is it blue?      Is bigger than a breadbox?      Is it a car?      Endpoint
Y                    Y                                         Y                      You win.
Y                    Y                                         N                      You lose.
Y                    N                                         N/A              You lose.
N                   N/A                                         N/A              You lose.


1. There will be a random number of questions (not always 3)
2. The questions will always be Y or N




Data:

select 1 TreeID, 1 Node, 'Is it blue?' Question, 'YES' Result, 2 NextNode, '' EndPoint from dual union
select 1 TreeID, 1 Node, 'Is it blue?' Question, 'NO' Result, null NextNode, 'You lose.' EndPoint from dual union
select 1 TreeID, 2 Node, 'Is bigger than a breadbox?' Question, 'YES' Result, 3 NextNode, null EndPoint from dual union
select 1 TreeID, 2 Node, 'Is bigger than a breadbox?' Question, 'NO' Result, null NextNode, 'You lose.' EndPoint from dual union
select 1 TreeID, 3 Node, 'Is it a car?' Question, 'YES' Result, null NextNode, 'You win.' EndPoint from dual union
select 1 TreeID, 3 Node, 'Is it a car?' Question, 'NO' Result, null NextNode, 'You lose.' EndPoint from dual






Thanks for any help with this!!
0
Comment
Question by:kwieckii
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
10 Comments
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 1000 total points
ID: 34913393
try this :

select x.*, case when is_it_blue ='Y' and is_it_btbb = 'Y' and is_it_car ='Y' then 'You Win' else 'You Lose' end end_point
( select
case when question = 'Is it blue?' then 'Y' else 'N' end "is_it_blue",
case when question = 'Is bigger than a breadbox?' then 'Y' else 'N' end "is_it_btbb",
case when question = 'Is it a car?'  then 'Y' else 'N' end "is_it_car"
from your_table ) x

Thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34915429
You can't have a number of columns that might change depending on the results.
A SQL statement must know how many columns it will return when it is parsed.

0
 

Author Comment

by:kwieckii
ID: 34915977
I do have each question stored along with it's ID:

QuestionID      /               QuestionText
1                                     Is it blue
2                                     Is it bigger than a breadbox
3                                     Is it a car

Would it be possible to use all the questions stored in the Question_Table as column names?

I need to look back on another query (time lag/lapse) you helped with a while ago.  There was something similar embedded in the code - but the way I think it was handled back then was to use explicit values ('Y','N').

I think a pivot-type function would help but we don't have 11 yet.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:kwieckii
ID: 34916884
Thanks nav- that would definitely work if i always knew the questions.  However - the questions always change.

If it comes down to it - I may need to do it that way.  Still looking.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34917754
ok. curious to see what approach you are going to pick up.
0
 

Author Comment

by:kwieckii
ID: 34926999
Still not sure - this one's tough... I've looked at different options - SQL Server, Access, Excel.. what may help is an unsummarized pivot table.

One thing I'm thinking of is trying a reverse tree traverse (starting from the endpoint) to fill in all the answers.

My challenge is taking a decision tree structure and flattening it out into a matrix. (I wish I paid more attention in my data structures courses).

So - I'm at a crawl on what to do with this.  I did look at your suggested code and I'm trying to make the endpoint derived from the data instead of specifically doing a case for 'You win/lose' - too much room for error for me there.

I do give up on having dynamic columns (or questions)... if a question is added, I'll need to revise accordingly.

Thanks for the input.
0
 

Author Comment

by:kwieckii
ID: 34927031
It's funny too - that in looking back at an older query - I was wishing I had 10g - now that I have 10g, I wish I had 11.  (also reminded me that now that I have 10g - I should probably revisit that older query to try to improve it).
0
 

Author Comment

by:kwieckii
ID: 34963915
I'm finally at the point where I'm ready to start thinking of translating a value in a row to a column name.  I first needed to overcome the parent/child relationships.  I'm accepting that I can't have a dynamic column creation - maybe in Oracle 15?  I wound up using the endpoint and traveling up the tree instead of down... here it is in case anyone has a similar need.  I needed to identify the row in case a path led to the same result - so I used the rownum to distinguish between the two.


select replace(sys_connect_by_path(endpoint,'/'),'/',''), result, question
from
(
select case when endpoint is not null then rownum || ' ' || endpoint else '' end endpoint, node, nextnode, question, result
from
(select 1 Node, 'Is it blue?' Question, 'YES' Result, 2 NextNode, null endpoint from dual union
select 1 Node, 'Is it blue?' Question, 'NO' Result, null NextNode, 'You lose.' endpoint from dual union
select 2 Node, 'Is it bigger than a breadbox?' Question, 'YES' Result, 3 NextNode, null endpoint from dual union
select 2 Node, 'Is it bigger than a breadbox?' Question, 'NO' Result, null NextNode, 'You might lose' endpoint from dual union
select 3 Node, 'Is it a car?' Question, 'YES' Result, null NextNode, 'You win' endpoint from dual union
select 3 Node, 'Is it a car?' Question, 'NO' Result, null NextNode, '      You lose sometimes.' endpoint from dual
) testdata
)
START WITH endpoint IS not NULL
CONNECT BY PRIOR node = NextNode

This is the output for this query:

1 You lose.                                                 NO      Is it blue?
6 You win                                                 YES      Is it a car?
6 You win                                               YES      Is it bigger than a breadbox?
6 You win                                               YES      Is it blue?
5 You lose sometimes.                                 NO      Is it a car?
5 You lose sometimes.                               YES      Is it bigger than a breadbox?
5 You lose sometimes.                                 YES      Is it blue?
3 You might lose                                         NO      Is it bigger than a breadbox?
3 You might lose                                         YES      Is it blue?


0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 34964011
>>>  translating a value in a row to a column name

You can't do that via sql.


>>> I'm accepting that I can't have a dynamic column creation - maybe in Oracle 15?

Not unless a new SQL standard comes out.  It's not a limit Oracle imposed on itself,
i's just part of SQL,  the statement must have a known number of results per row

A procedure could be written to generate sql for you.  Is that what you want?

If you are willing to accept a fixed set of columns for output ouput, do you still want something like the original example?  Or are you now looking for something different?  Based on the query you have shown in your last post I'm not sure where you're headed now.

0
 

Author Comment

by:kwieckii
ID: 34964642
Looking at the code submitted above, it looks like I'd be calculating an answer based on results I would find in the table.  If I'm reading this correctly, that's not quite what I'm getting at.  :

select x.*, case when is_it_blue ='Y' and is_it_btbb = 'Y' and is_it_car ='Y' then 'You Win' else 'You Lose' end end_point
( select
case when question = 'Is it blue?' then 'Y' else 'N' end "is_it_blue",
case when question = 'Is bigger than a breadbox?' then 'Y' else 'N' end "is_it_btbb",
case when question = 'Is it a car?'  then 'Y' else 'N' end "is_it_car"
from your_table ) x




would this be the only way I could accomplish what I'm trying to do?  (let me know if I'm wrong - but if a procedure were written, I'd need to have DML access?)


select
nvl(max(Isitblue), 'Yes or No') "Is it blue?",
nvl(max(biggerthanabb), 'Yes or No') "Is it bigger than a bb?",
nvl(max(Isitacar), 'Yes or No') "Is it a car?",
answer

from
(

select  

decode(question, 'Is it blue?', result) Isitblue,
decode(question, 'Is it bigger than a breadbox?', result) biggerthanabb,
decode(question, 'Is it a car?', result) Isitacar,
replace(sys_connect_by_path(endpoint,'/'),'/','') Answer

from (
select case when endpoint is not null then  '(' || rownum || ')' || endpoint else '' end endpoint, node, nextnode, question, result
from
(select 1 Node, 'Is it blue?' Question, 'YES' Result, 2 NextNode, null endpoint from dual union
select 1 Node, 'Is it blue?' Question, 'NO' Result, null NextNode, 'You lose.' endpoint from dual union
select 2 Node, 'Is it bigger than a breadbox?' Question, 'YES' Result, 3 NextNode, null endpoint from dual union
select 2 Node, 'Is it bigger than a breadbox?' Question, 'NO' Result, null NextNode, 'You lose' endpoint from dual union
select 3 Node, 'Is it a car?' Question, 'YES' Result, null NextNode, 'You win' endpoint from dual union
select 3 Node, 'Is it a car?' Question, 'NO' Result, null NextNode, 'You lose sometimes.' endpoint from dual
) testdata)
START WITH endpoint IS not NULL
CONNECT BY PRIOR node = NextNode
)

group by Answer


This would give me what I was hoping to get at in the original example:


Is it blue?                   Is it bigger than a bb?                       Is it a car?      ANSWER
NO                              Yes or No                                      Yes or No       (1)You lose.
YES                              NO                                                      Yes or No         (3)You lose
YES                             YES                                                       NO                       (5)You lose sometimes.
YES                             YES                                                       YES               (6)You win



(I'll probably wind up taking off the parens (1) in the answer - those were only used to make sure I didn't overlap the rules - in the case where two paths were used to get to the same answer.




0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question