Home » SQL & PL/SQL » SQL & PL/SQL » Connect By Clause - Child -> Parent (merged)
Connect By Clause - Child -> Parent (merged) [message #330030] Fri, 27 June 2008 06:44 Go to next message
thambi.friends@gmail.com
Messages: 2
Registered: June 2008
Junior Member
Hi,

I need to write a SQL with CONNECT BY caluse which display the parent and child folder relationship. I have written the SQL . Condition is that if a folder has atleast one file, then folder and it's file has to be displayed ( I don't have folder leve permission entry in npf_cust_files_access ) .


SELECT a.file_id AS file_id,
a.file_name AS file_name,
a.file_type AS file_type,
LEVEL,
a.file_size AS file_size,
TO_CHAR(a.upd_tm, 'MM/DD/YYYY') AS last_update_date,
a.upd_by AS last_update_user,
a.parent_folder_id AS parent_folder_id,
a.file_format AS file_format,
a.user_type_dcde AS user_type_dcde,
CASE a.rstr_accss_flag
WHEN 'Y'
THEN DECODE(( SELECT COUNT(ROWID)
FROM npf_cust_files_access b
WHERE b.cust_sk = '100976'
AND (b.user_sk = '102656' or a.creat_by = ( select login_id from npf_user where user_sk = '102656' ))
AND b.file_id = a.file_id), 0, 'N', 'Y')
ELSE 'Y' END "Access", NVL(a.del_flag, 'L') AS del_flag ,
a.creat_by
FROM npf_cust_files a

START WITH a.file_id IN (
SELECT d.file_id
FROM npf_cust_files d
, npf_cust_files_access b where /* d.file_id = b.file_id and */ b.cust_sk = '100976'
and ( (b.user_sk = '102656' and d.file_id = b.file_id )
or d.creat_by = ( select login_id from npf_user where user_sk = '102656' ) )
AND d.file_type != 'H')
CONNECT BY PRIOR a.parent_folder_id = a.file_id
ORDER siblings BY a.file_type , UPPER(a.file_name)

If a folder contain only one file , i get the expected result.

FILE_ID FILE_NAME FILE_TYPE LEVEL FILE_SIZE LAST_UPDATE_DATE LAST_UPDATE_USER PARENT_FOLDER_ID


7032 level 1 D 1 0 5/7/2008 WMG1 7031
7031 thambi D 2 0 5/7/2008 WMG1 [NULL]


But If a folder contain more than one file, i am getting extra unncessary record . below the result..

FILE_ID FILE_NAME FILE_TYPE LEVEL FILE_SIZE LAST_UPDATE_DATE LAST_UPDATE_USER PARENT_FOLDER_ID
7432 test_thambi D 1 0 6/25/2008 WMG1 [NULL]
7433 info.pdf F 1 1399176 6/25/2008 WMG1 7432
7432 test_thambi D 2 0 6/25/2008 WMG1 [NULL]
7436 kk.pdf F 1 1399176 6/25/2008 WMG1 7432
7432 test_thambi D 2 0 6/25/2008 WMG1 [NULL]


I need to get in this format
FILE_ID FILE_NAME FILE_TYPE LEVEL FILE_SIZE LAST_UPDATE_DATE LAST_UPDATE_USER PARENT_FOLDER_ID

7433 info.pdf F 1 1399176 6/25/2008 WMG1 7432
7436 kk.pdf F 1 1399176 6/25/2008 WMG1 7432
7432 test_thambi D 2 0 6/25/2008 WMG1 [NULL]


Graphically..

Current result

Folder 1
|_ f1
Folder 1
|_ f2

Issue: wheneve file is displaying and it's folder also displaying for each file. I just need to display only one folder record for all the files. Below is expected result..

Expected result

Folder 1
|_ f1
|_ f2

SQL should not return the folder every time. For all the files , it should return only one folder record
Re: Connect By Clause [message #330031 is a reply to message #330030] Fri, 27 June 2008 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Connect By Clause - Child -> Parent [message #330038 is a reply to message #330030] Fri, 27 June 2008 07:25 Go to previous messageGo to next message
thambi.friends@gmail.com
Messages: 2
Registered: June 2008
Junior Member
Hi,

I have an doubt on CONNECT BY Clause Quries.
select * from cust_files connect by PRIOR file_id = parent_folder_id;
Above SQL would return the folder and it's files in tree level structure.
I have a permission table which has the permission entries for files , NOT for folders.

Example
D
|_ F1
|_ F2
|_ F3
In the above structure, F2 and F3( Files) only having the permission entries
F1 does not have permission so result should be
D
|_F2
|_F3
I need to get the above result. that means based upon the child record, I need to fetch the parent folder.

If I write the quries like this
select * from cust_files connect by PRIOR parent_folder_id;
= file_id
it gives result as follows
D
|_F2

D
|_F3
For every file, it gives the folder record
But I need only one folder record regardless of how many files are being selected. Usually Using the parent record, we will select the child records. Here Using child record , we need to get the parent record

Expected output

D
|_F2
|_F3
Re: Connect By Clause - Child -> Parent [message #330043 is a reply to message #330038] Fri, 27 June 2008 07:35 Go to previous message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You repost it but still don't follow the guidelines and what has been requested.

In addition directory/files hierarchy has already been answered here several times.

Regards
Michel
Previous Topic: How can i find if two Tables are Equal to each other?
Next Topic: getting error while opening procedure or package
Goto Forum:
  


Current Time: Fri Dec 09 12:06:01 CST 2016

Total time taken to generate the page: 0.08549 seconds