Solved

parent/child transform query (oracle 10g)

Posted on 2011-02-16
10
825 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
  • 6
  • 2
  • 2
10 Comments
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 250 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 73

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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 73

Assisted Solution

by:sdstuber
sdstuber earned 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now