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
Help with page encoding issue and weird characters
I'm trying to write a screen scraper and when I pull out the lines of the html file that I'm interes
ME54/ME54N Conditional check for Release of PR
Hi Gurus,
My requirement is to put a check on the release of the PR with respect to the c
Help With editting and deleting form
Hallo !!
So look at this image :
http://img194.imageshack.us/img194/8272/snapshot5f.pn
RadioButtonList item spacing
I have a RadioButtonList and I can't put any spacing between the items. They are arranged verticall
try catch error
I have been trying try catch but, I can't get it to work. The code opens a non existing file called
generate seo frienly url from a text like wordpress
Convert: "åäö" to "aao", "space" to "-", "!?&q
Printer configuration
Hi All,
Suppose one of the user deleted the printer settings unfortunatly ,then what is the pro
newbie error
what is wrong with this code ?
<html>
<body>
<?
Breaking results into week blocks
I have a set of dates (and times), which are returned from a mySQL query.
These usually span
Ajax Error since Upgrading to 3.5
Ever since upgrading my site to .NET 3.5 (I needed LINQ), I've been getting this annoying error on o
