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
XML Grouping
I'm using xml_parse_into_struct to get all my elements, but now I need to group them. For example, h
How do I send data using an html link
Hi
If I have
<a href="main_file.php">
How do I send data t
default SAP userid
hi,, I just like to know if it is ok to use the default SAP user id (SAP*)?
SQL query not working well
Hi,
I made a small table with 5 rows and want to make them an sql consult using rownum
Why use OOP?
Can someone explain to me why I should use OOP instead of procedure based code.
Im building a
Multiple Dropdown Selections
I have a form that let's a user insert a page with the ability to select categories. I want them to
Mail Form receiving emails with no content
Hi, I hope someone here can help me.
I have a simple form in my website, it was working OK, after
Uploading files/images via forms
Hey all, I am building a database for work that will contain some minor data on plant species along
Hardcopy printing is to wide
I want to make a hardcopy at runtime of my active form like this
Not Loading Function Into Div
I'm not sure whether to put this under the php forum or ajax forum but because I tink it's more of a
