All possible combinations of Strings from table in PL/SQL


Posted on 16th Feb 2014 07:03 pm by admin

Hi,

I am trying to figure out how to build a list of all possible string combinations from values stored in a single table.

There are multiple "levels" (a level being the order in which the value appears in the outputted string) and multiple values per level...

I'm using Oracle 10g R2, and my tables structure is as follows:
--not the best naming conventions, but it works for discussion... CREATE TABLE FamilyValues ( myID INTEGER NOT NULL, FamilyID INTEGER NOT NULL, myLevel INTEGER NOT NULL, myValue VARCHAR2(10) ) insert into familyvalues values(1,1,1,'I',); insert into familyvalues values(2,1,1,'E',); insert into familyvalues values(3,1,2,'2',); insert into familyvalues values(4,1,2,'J',); insert into familyvalues values(5,1,2,'B',); insert into familyvalues values(6,1,3,'0',); insert into familyvalues values(7,1,3,'1',); insert into familyvalues values(8,1,3,'2',); insert into familyvalues values(9,1,3,'3',); insert into familyvalues values(10,1,3,'4',); insert into familyvalues values(11,1,3,'5',); insert into familyvalues values(12,1,3,'6',); insert into familyvalues values(13,1,3,'7',); insert into familyvalues values(14,1,3,'N',); insert into familyvalues values(15,1,3,'T',); insert into familyvalues values(16,1,3,'V',); insert into familyvalues values(17,1,3,'W',); insert into familyvalues values(18,1,4,'0',); insert into familyvalues values(19,1,4,'1',); insert into familyvalues values(20,1,4,'2',); insert into familyvalues values(21,1,4,'3',); insert into familyvalues values(22,1,4,'4',); insert into familyvalues values(23,1,4,'B',); insert into familyvalues values(24,1,4,'D',); insert into familyvalues values(25,1,4,'F',); insert into familyvalues values(26,1,4,'H',); insert into familyvalues values(27,1,4,'J',); insert into familyvalues values(28,1,4,'K',); insert into familyvalues values(29,1,4,'L',); insert into familyvalues values(30,1,4,'M',); insert into familyvalues values(31,1,4,'N',); insert into familyvalues values(32,1,4,'P',); insert into familyvalues values(33,1,4,'R',); insert into familyvalues values(34,1,4,'T',); insert into familyvalues values(35,1,4,'V',); insert into familyvalues values(36,1,4,'W',); insert into familyvalues values(37,1,4,'X',); insert into familyvalues values(38,1,4,'Y',); insert into familyvalues values(39,1,4,'Z',);

The results i would like to get are, for the sample data above (using only 3 levels for a manageable amount of data):

I 2 0 I 2 1 I 2 2 I 2 3 I 2 4 I 2 5 I 2 6 I 2 7 I 2 N I 2 T I 2 V I 2 W E J 0 E J 1 E J 2 E J 3 E J 4 E J 5 E J 6 E J 7 E J N E J T E J V E J W
my best attempt at this thus far is :
select A.myValue || '-' || B.myValue || C.myValue || D.myValue from FamilyValues A FULL JOIN FamilyValues B ON (B.myLevel -1) = A.myLevel FULL JOIN FamilyValues C ON (C.myLevel -1) = B.myLevel FULL JOIN FamilyValues D ON (D.myLevel -1) = C.myLevel WHERE A.myValue IS NOT NULL AND B.myValue IS NOT NULL AND C.myValue IS NOT NULL AND D.myValue IS NOT NULL

i have read over the post at (http://forums.oracle.com/forums/thread.jspa?messageID=3845767#3845767) but believe this is a slightly different implementation...

I would like to be able to recusively go through the table, and formulate all possible combinations with each values from the table in the positions designated by the level...

No comments posted yet

Your Answer:

Login to answer
251 Like 45 Dislike
Previous forums Next forums
Other forums

[newb] Image hosting help.
Hey there, I'm new to php, I know some basics and i can code PWN, I'm only 14 but I'm interested in

$_FILES array
Ok,

I just started experimenting with PHP today. My goal right now is to have people be able

How to implement HTTP connection timeout
Hi All,I would like to implement an HTTP connection timeout.

I am working on a application wh

session variable problem
Session variable is not working in Fire fox i am getting null value but it is working fine in IE.

Interpret Order
Hello all,

I'm wondering if I have this:

Code: $switch = array(
'one' =>

I need an iframe that calls up a different page according to the date
hi

I don't know any php (only html) and I urgently need to do the following:

I need a

Call db table from any PHP file
Hi,

I want to be able to call a database table that will be setup in another file called init

Loop column after 2 results
Hello All.

Here is what I have:

Client ID Company name Clien

I have a parse error in this query help..
Code: $query1="INSERT INTO `rating` (`item_name`, `rating`, `ip_address`, `date_rated`) VALUES

upload image name with extension using php
hi frds..

<input id="file1" type="file" name="file[]" &a

Sign up to write
Sign up now if you have flare of writing..
Login   |   Register
Follow Us
Indyaspeak @ Facebook Indyaspeak @ Twitter Indyaspeak @ Pinterest RSS



Play Free Quiz and Win Cash