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 
45 
Other forums
The page should be expire when cilck back button
hi,
i'm new to php world.
i create user registration page.
when i submit it,data goes to my
Parse Error with doctype
I'm getting a parse error with this simple code. I don't get it. It worked one time then when I relo
how to read and write into a word document using php...?
hi,
I need,reading and writting into a word document using php.
Thank u inadva
unexpected T_SL without a shift left token
Nothing too see here, I'm an idiot and resolved the problem.
a function to check directory depth
I'm working on a php script to upload files in to a set directory.
the user can select to upload
Data Function is Its Not working IN IE8
This is my first time to use formums. I hope i can get solution for this problem.
view plaincopy to
Save username into DB
OkaY so I got my blog to actually save the posts and whatever, all I need now is for it to keep the
Please help - should be a simple fix.. driving me nuts
Everything seemed to be working fine. I have a table, it alphabetically lists a bunch of cities and
Javascript or not?
How many people prefer javascript/ajax sites? How many prefer the good old fashion straight php sit
Help uploading .JPG
I have a problem with upload images when the ending is .JPG capitalized.. I really don't know what c
