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

Email Form Syntax Issue
I need the TO: in email to display To: CEO instead of To: abc@mail.com

How to alter the scri

background color imagefill
Hello

I would like to ask you why I see this square in red color just in my local xampp insta

Problems generating word documents on server side for security reasons
I have a problem with word documentation generation when generating a word document (docx) with PHP.

Problem related to Creation of PDF File?
Hi All,
I am facing a problem related to creation pdf file. when I am creating a pdf file of do

Retrieving innerHTML with cURL?
Hey all (sorry I know I'm a leecher, but I soon won't be. This is my first PHP project, but not my l

Undefined variable when using $_SERVER['PHP_SELF']
Hi guyz, please suggest me something...
On first.php I have one input field NAME, and on posting

Find current logon time
Hi,
in my sql script i want to query and find out the logon time of the current session.
<

how to get IP address?
hey guys,

i have this code which is supposed to get me the IP address of my site visitors:

utf8_general_ci error ??
I have all kinds of data to be inserted in database table

//I am getting this error

Yo

Thread in PHP
Thread in PHP
Some basic use and basic code for thread in php
This is my question?

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