Home » SQL & PL/SQL » SQL & PL/SQL » Get all possible paths (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)
Get all possible paths [message #569987] Sun, 04 November 2012 06:02 Go to next message
manubatham20
Messages: 450
Registered: September 2010
Location: Champaign, IL
Senior Member

Hi,

Suppose I have 4 Inputs 'A','B','C' and 'D'.

Problem is I have to get all the path from
1st 'A''B''C''D' to 'A'
2nd 'A''B''C''D' to 'B'
3rd 'A''B''C''D' to 'C'
4th 'A''B''C''D' to 'D'

For 1st one all the possible paths will be:

ABCD --> ABC --> AB --> A
ABCD --> ABC --> AC --> A
ABCD --> ABD --> AB --> A
ABCD --> ABD --> AD --> A
ABCD --> ACD --> AC --> A
ABCD --> ACD --> AD --> A
... same approach for second path and so on...

The inputs may vary in number, like they may be 'A','B','C','D','E'... so on.

Nothing in my mind to solve this problem, please suggest.

Note: This may not seems to be a Oracle problem, but I have to do it in Oracle as my further steps will depend on this.

Thanks,
Manu
Re: Get all possible paths [message #569988 is a reply to message #569987] Sun, 04 November 2012 08:34 Go to previous messageGo to next message
BlackSwan
Messages: 22709
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Get all possible paths [message #569992 is a reply to message #569988] Sun, 04 November 2012 09:22 Go to previous messageGo to next message
manubatham20
Messages: 450
Registered: September 2010
Location: Champaign, IL
Senior Member

Hi BlackSwan,

Please let me know which guideline I have violated, so that I can correct accordingly.

Thanks,
Manu
Re: Get all possible paths [message #569993 is a reply to message #569992] Sun, 04 November 2012 09:29 Go to previous messageGo to next message
BlackSwan
Messages: 22709
Registered: January 2009
Senior Member
error of omission
where did you comply with #9 or #10?
Re: Get all possible paths [message #570008 is a reply to message #569992] Sun, 04 November 2012 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 58911
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL and PL/SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Get all possible paths [message #570018 is a reply to message #569987] Sun, 04 November 2012 14:03 Go to previous message
Solomon Yakobson
Messages: 1999
Registered: January 2010
Senior Member
Well, you could use combination of hierarchical query + model:

with inputs as (
                select 'A' point from dual union all
                select 'B' point from dual union all
                select 'C' point from dual union all
                select 'D' point from dual
               ),
          t as (
                select  rownum r,
                        max(level) over() maxl,
                        level l,
                        connect_by_root point,
                        replace(sys_connect_by_path(point,','),',') path
                  from  inputs
                  connect by nocycle point != prior point
               )
select  path
  from  (
         select  l,
                 maxl,
                 path
           from  t
           model
           dimension by(r)
           measures(l,maxl,path)
           rules(
                 path[any] order by r = case l[cv()]
                                          when 1 then path[cv()]
                                          when l[cv() - 1] + 1 then path[cv()] || '-->' || path[cv() - 1]
                                          else path[cv()] || substr(path[cv() - 1],instr(path[cv() - 1],'-->',-1,l[cv()]))
                                        end
                )
        )
  where l = maxl
  order by path
/

PATH
----------------------------------------
ABCD-->ABC-->AB-->A
ABDC-->ABD-->ABC-->AB-->A
ACBD-->ACB-->AC-->AB-->A
ACDB-->ACD-->AC-->AB-->A
ADBC-->ADB-->AD-->AB-->A
ADCB-->ADC-->AD-->AB-->A
BACD-->BAC-->BA-->B
BADC-->BAD-->BAC-->BA-->B
BCAD-->BCA-->BC-->BA-->B
BCDA-->BCD-->BC-->BA-->B
BDAC-->BDA-->BD-->BA-->B

PATH
----------------------------------------
BDCA-->BDC-->BD-->BA-->B
CABD-->CAB-->CA-->C
CADB-->CAD-->CAB-->CA-->C
CBAD-->CBA-->CB-->CA-->C
CBDA-->CBD-->CB-->CA-->C
CDAB-->CDA-->CD-->CA-->C
CDBA-->CDB-->CD-->CA-->C
DABC-->DAB-->DA-->D
DACB-->DAC-->DAB-->DA-->D
DBAC-->DBA-->DB-->DA-->D
DBCA-->DBC-->DB-->DA-->D

PATH
----------------------------------------
DCAB-->DCA-->DC-->DA-->D
DCBA-->DCB-->DC-->DA-->D

24 rows selected.

SQL> 


SY.
Previous Topic: ERROR PLS-00306
Next Topic: FORALL INSERT not supported on remote tables
Goto Forum:
  


Current Time: Wed Aug 27 14:39:00 CDT 2014

Total time taken to generate the page: 0.12560 seconds