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

This must be easy , pulling the last record of the day, every day, from a txt
I have a txt file logging weather data every minutes (so 1 record per minute). I want to extract the

Convert array to string and write to csv file
I am looking to write a MySQL query to a csv file.

At the minute I:

check for the orig

How to clone an SAP ECC using bacup tape on z/os?
We want to clone our production to a different SID using backup tape.

php call servlet
I have done a php backup application .
So there is a form that user pick some files to zip and d

sql problems
I am having a small problem with my sql statement. it is inserting into 4 of the fields.

fie

Array becomes unset by itself?
Hi,

I have a bit of experience with PHP but not extensive. It seems I cannot store any items

Snapshot from video
anyone knw how to make a snapshot maker from avi n mkv format.
for example if i give a direct lin

Hardcopy printing is to wide
I want to make a hardcopy at runtime of my active form like this

Beginners syntax and loop questions - help please!
Hi everyone,

I have some problems with php code. I'm currently in the middle of learning php,

I am stumped
OK. Here is what I am trying to do. I have the conditions set, and if the conditions are met, I want

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