Home » SQL & PL/SQL » SQL & PL/SQL » CONNECT BY PRIOR
CONNECT BY PRIOR [message #243598] Thu, 07 June 2007 21:33 Go to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
hi all,
i have question.
I am having a table like this.
file_table:
-----------
parentid,dataid,file_folder,filename
------------------------------------
99,100,folder,mainfolder
100,110,file,abc.txt
100,111,file,bcd.txt
100,112,folder,folder1
112,115,file,folder1_file.txt
112,116,folder,folder2

so this table is similar to the files & folders in a filesystem
files abc.txt,bcd.txt & folder folder1 were inside folder 'mainfolder'.
i want to generate a output in the form shown below..(for a given root folder)
output:
-------
Say for example the given root folder is 'mainfolder' and id =100

i want to generate an output which will show like this..

mainfolder\abc.txt
mainfolder\bcd.txt
mainfolder\folder1
mainfolder\folder1\folder1_file.txt
mainfolder\folder1\folder2

Is this possible with connect by prior in oracle..
i tried a lot..but icannot..
pls help

Mahesh






Re: CONNECT BY PRIOR [message #243600 is a reply to message #243598] Thu, 07 June 2007 21:48 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
check it for connect by prior

http://www.orafaq.com/forum/m/229379/105911/?srch=CONNECT+BY+PRIOR#msg_229379
Re: CONNECT BY PRIOR [message #243612 is a reply to message #243598] Fri, 08 June 2007 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post your Oracle version (4 decimals).
Please read and apply How to format your posts.

This is possible with SYS_CONNECT_BY_PATH function (if you are in >=9.0) with the use of CONNECT_BY_ISLEAF pseudo-column (if you are in 10g and only want the leaves).

Btw, if you give CREATE TABLE and INSERT statements for your test case, you will likely have more answers to your question.

Regards
Michel

[edit: fix URL]

[Updated on: Fri, 08 June 2007 01:36]

Report message to a moderator

Re: CONNECT BY PRIOR [message #243621 is a reply to message #243598] Fri, 08 June 2007 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something like:
SQL> select sys_connect_by_path(filename,'/') path
  2  from file_table
  3  connect by prior dataid = parent_id 
  4  start with parent_id = 99
  5  /
PATH
----------------------------------------------------
/mainfolder
/mainfolder/abc.txt
/mainfolder/bcd.txt
/mainfolder/folder1
/mainfolder/folder1/folder1_file.txt
/mainfolder/folder1/folder2

6 rows selected.

Regards
Michel
Re: CONNECT BY PRIOR [message #243660 is a reply to message #243598] Fri, 08 June 2007 05:08 Go to previous messageGo to next message
bhushanbagul
Messages: 2
Registered: February 2007
Junior Member
Dear Mahesh

Please try with below query, it'll display the required result.

SELECT LTRIM (SYS_CONNECT_BY_PATH (file_name, '\'), '\') PATH FROM tbl_file_folder
START WITH data_id IN (100)
CONNECT BY PRIOR data_id = parent_id;

Regds,
Bhushan
Re: CONNECT BY PRIOR [message #243665 is a reply to message #243660] Fri, 08 June 2007 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome on the forum.

Note it is useless to give an answer that have been posted 4 hours earlier.

Regards
Michel
Re: CONNECT BY PRIOR [message #243671 is a reply to message #243665] Fri, 08 June 2007 05:43 Go to previous messageGo to next message
bhushanbagul
Messages: 2
Registered: February 2007
Junior Member
Dear Michel

Thanks for welcome.

First i ran the query posted by you but was giving error and the result that u shown is also not proper. Thats why i have posted the query which is giving proper result.
Re: CONNECT BY PRIOR [message #243685 is a reply to message #243671] Fri, 08 June 2007 07:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please, if you get an error post it, it will be useful for us to know what can be the problem on some versions.

I didn't post exactly the result because my policy is not to give a full solution but the way to achieve (it'd be easy for me to put \ instead of /, to add a substr...).
2 reasons for this policy:
- A solution (even a very good one) may fail in the real site (and you see my query failed for you when it works for me)
- OP will learn much more if he modifies the query to fit his requirements as he is forced to study it instead of just copy it.

Regards
Michel

Re: CONNECT BY PRIOR [message #243697 is a reply to message #243598] Fri, 08 June 2007 08:32 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
guys,
I am using oracle 10g.
thanks a alot guys..
Bhushan, it was excellent. Your query was returning exactly what i am expecting.

once again thanks alot guys

Michel, I agree with your 2nd reason.

Thats gr8, today i learned something interesting.

great day
Mahesh
Re: CONNECT BY PRIOR [message #244437 is a reply to message #243598] Tue, 12 June 2007 15:42 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Hi,
I need to extend the functionality of the bhushan's sql query.
in addition to the table which i have given in my first mail, there is another table like below.

version_table:
---------------
docid,filename,versionno,filesize,createddate
110,abc.txt,1,100,01-01-07
110,abc.txt,2,120,01-02-07
110,abc.txt,3,120,01-06-07
111,bcd.txt,1,50,01-10-07
111,bcd.txt,2,123,01-11-07
111,bcd.txt,3,150,01-11-07
111,bcd.txt,4,200,01-12-07
111,bcd.txt,5,300,01-13-07
115,folder1_file.txt,1,250,01-10-07
115,folder1_file.txt,2,300,01-10-07

please note that for folder(file_folder column value in first table is folder) there will not be having any record in this version_table(as folders will not have versions).

Now my question is :
Can i use the sql query (provided by bhushan) to get the following output:

path,version,size,createddate
/mainfolder
/mainfolder/abc.txt,1,100,01-01-07
/mainfolder/abc.txt,2,120,01-02-07
/mainfolder/abc.txt,3,120,01-06-07
/mainfolder/bcd.txt,1,50,01-10-07
/mainfolder/bcd.txt,2,123,01-11-07
/mainfolder/bcd.txt,3,150,01-11-07
/mainfolder/bcd.txt,4,200,01-12-07
/mainfolder/bcd.txt,5,300,01-13-07
/mainfolder/folder1
/mainfolder/folder1/folder1_file.txt,1,250,01-10-07
/mainfolder/folder1/folder1_file.txt,2,300,01-10-07
/mainfolder/folder1/folder2

Basically i want to join the file_table with the version_table and get the output like above.
is this possible?
thanks
Mahesh
Re: CONNECT BY PRIOR [message #244452 is a reply to message #244437] Tue, 12 June 2007 23:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at SQL Reference, Chapter 9 SQL Queries and Subqueries, Section Joins, especially Outer Joins

Regards
Michel
Re: CONNECT BY PRIOR [message #244542 is a reply to message #243598] Wed, 13 June 2007 04:50 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Yes, I have done this.
i used temp table and joined with the main table.
thanks for directing me
Mahesh
Re: CONNECT BY PRIOR [message #244543 is a reply to message #244542] Wed, 13 June 2007 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you found the query post it it will be helpful for other readers.

Regards
Michel
Re: CONNECT BY PRIOR [message #245768 is a reply to message #244543] Mon, 18 June 2007 14:01 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
answer to the above question:

I have used a temp table like this

select * from version_table v, (SELECT LTRIM (SYS_CONNECT_BY_PATH (file_name, '\'), '\') PATH,data_id FROM tbl_file_folder
START WITH data_id IN (100)
CONNECT BY PRIOR data_id = parent_id) temp
where temp.data_id = v.docid
Re: CONNECT BY PRIOR [message #245769 is a reply to message #243598] Mon, 18 June 2007 14:03 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
any ideas.. to do the following..
note: this is only for files..
ex:
parentfolder,file_name
-----------------
/mainfolder ,abc.txt
/mainfolder,bcd.txt
/mainfolder/folder1, folder1_file.txt

instead of whole file path i just need a parent path..

thanks,
mahesh
Re: CONNECT BY PRIOR [message #245770 is a reply to message #245769] Mon, 18 June 2007 14:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you just want the parent directory then you don't need "connect by" just auto-join the table on parent_id=dataid.

Regards
Michel
Re: CONNECT BY PRIOR [message #245774 is a reply to message #243598] Mon, 18 June 2007 14:27 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
hi michel,

the parent directory should include the total path from the parent folder specified in the connect by cluase.
Example: /rootfolder/mainfolder/secondfolder/folder1

thanks,
Mahesh
Re: CONNECT BY PRIOR [message #245777 is a reply to message #245774] Mon, 18 June 2007 14:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You just want to replace the last "/" by ","?

Regards
Michel

[Updated on: Mon, 18 June 2007 14:45]

Report message to a moderator

Re: CONNECT BY PRIOR [message #245791 is a reply to message #243598] Mon, 18 June 2007 16:02 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
nope,
the out put is 2 columns (parentfolder,file_name)
typically the out-put looks like this.

parentfolder file_name
-----------------
/mainfolder abc.txt
/mainfolder bcd.txt
/mainfolder/folder1 folder1_file.txt

Thanks,
Mahesh P
Re: CONNECT BY PRIOR [message #245829 is a reply to message #245791] Mon, 18 June 2007 23:35 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read again my first post I gave you a link to format your post, USE IT then it will prevent us from asking and asking and asking again for what you want and waste our time.

Now, with the answer we gave you should be able to find a solution. Post what you tried.

Regards
Michel
Previous Topic: Need Oracle Proc to return mult rows
Next Topic: Not urgent (How to ignore a set error code?)
Goto Forum:
  


Current Time: Tue Dec 06 16:04:11 CST 2016

Total time taken to generate the page: 0.08701 seconds