Home » SQL & PL/SQL » SQL & PL/SQL » CONNECT BY PRIOR
| CONNECT BY PRIOR [message #243598] |
Thu, 07 June 2007 21:33  |
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 #243660 is a reply to message #243598] |
Fri, 08 June 2007 05:08  |
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 #243671 is a reply to message #243665] |
Fri, 08 June 2007 05:43 |
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 #243697 is a reply to message #243598] |
Fri, 08 June 2007 08:32 |
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 |
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 #245768 is a reply to message #244543] |
Mon, 18 June 2007 14:01 |
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 |
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 #245774 is a reply to message #243598] |
Mon, 18 June 2007 14:27 |
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 #245791 is a reply to message #243598] |
Mon, 18 June 2007 16:02 |
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 |
 |
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
Goto Forum:
Current Time: Thu Dec 12 23:26:40 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
