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
Ten Operator Syntax
Hi Guys,
I can't figure out why i am getting a parse error with this basic ten op code:
Probably a simple error...
I'm getting the error -- Parse error: syntax error, unexpected '{' in /home/content/c/s/t/csteffen24
Something like an INI editor or a DelimitedText-Editor
Hi all,
Am very, very, very new to PHP and not sure if I should be posting this to a Javascri
Can't shake the "Warning: include()" error
I've just uploaded my site to a new server and where I have PHP include tags in my HTML, the browser
Disable Scrollbar in TableControl
Hello everbody,
please can anybody help me - outherwise i'm going to become desperate :P<
How to change Time Zone
HI
I want to change the time zone of the server to another country.How can do that?
Thanks
Find only certain URLs from page ... regex (semi-complete script)
Hi guys,
What I need to do is take a page & extract all the URLs from the page &a
Reading waves
Hi,
I am trying to find a way of finding the highs and lows on a graph line, the line points
question about header() security
is is safe to just use the header() function to redirect someone if they are, say, not logged in? or
mysql_affected_rows() usage
Possibly a MySQL issue, but the function that is not behaving in the anticipated way is a PHP functi
