[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Recursive/Reselect using SQL

Posted on 2009-04-28
5
Medium Priority
?
270 Views
Last Modified: 2013-12-07
Hi,

I have a problem which I am not sure can be solved by basic SQL. I can't write PL/SQL sp or function.

I have a table like this:

Col1, Col2, Col3
A, X, 3
B, Y, 2
C, W, 1

Col3 needs to dictate how many times that row is selected. The SQL query should result something like this for the above content:


A, X
A, X
A, X
B, Y
B, Y
C, W

Can this be done?
0
Comment
Question by:infa007
  • 3
  • 2
5 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 24251594
try this...
change the level <= 10 to a number large enough for the largest col3 in your table


select col1,col2 from your_table t , (select level n from dual connect by level <= 10)
where n <= col3
order by col1,col2
0
 

Author Comment

by:infa007
ID: 24251699
that works! I just made a small change:

select col1,col2 from your_table t , (select level n from dual connect by level <= (select max(col3) from your_table)
where n <= col3
order by col1,col2

I assigning the points to you but please explain how does this work?
Thanks.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24251862
pull out the inline view


(select level n from dual connect by level <= 10)

or your modification


(select level n from dual connect by level <= (select max(col3) from your_table)

you'll see that the connect by level  clause simply generates a list of numbers.
Normally you use the connect by clause to create parent-child references
Here the "children" are just the next row.

using my example  

dual has 1 row,  so level = 1 for that row and 1 <= 10 so it creates a child record  from dual
which still has 1 row, so it level = 2 for one row and still less than 10 so it creates another child record from dual, level = 3 for that one row and still less than 10 and so on until a child is created where it can't create a level 11 because it would violate the connect by.




0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24251879
then joining the list of numbers to your source table creates a cartesian join and the where clause narrows it down to just the number of copies of each row that you want
0
 

Author Comment

by:infa007
ID: 24252069
Makes sense. Thanks.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

834 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