Avatar of CalmSoul
CalmSoulFlag for United States of America asked on

folder path complex loop query

- This is complicated query, we have 3 tables document, folder, path
- Table 1 (i.e. document) can be easily joined by table 2 based on ID and LID and we can get FOLDID
- We take that FOLDID and use that to construct the path where that document is located.
- TABLE 3 (Path) Do a split or substring on FOLDID and get the FLDKEY in above example 56777...Based on FLDKEY save the FLDSTRING as the "current folder" document is located. Loop through using FLDKEY2 in path table and find the second current folder again ... loop through again until FLDKEY2 = 'ROOT'
Build-Folder-Path---Sheet1.csv
DatabasesOracle DatabaseSQL

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon
Sean Stuber

please post expected output that  corresponds to your sample input.

also your sample input seems incomplete given that we are supposed to look for "ROOT" elements but no such element exists in the sample.
ASKER
CalmSoul

Sean Stuber

SELECT p.fldstring,
       p2.fldstring,
       p3.fldstring,
       d.doc_name
  FROM document d
       INNER JOIN folder f ON d.id = f.lid
       INNER JOIN PATH p ON f.foldid = p.fldtype || '-' || p.fldkey
       LEFT JOIN PATH p2 ON p2.fldkey = p.fldkey2
       LEFT JOIN PATH p3 ON p3.fldkey = p2.fldkey2;



Searching for ROOT explicitly doesn't seem to be necessary, once you reach the root, the chain will stop anyway because there won't be anything to connect fldkey and fldkey2
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
CalmSoul

Sorry one change FOLDID = {ABCD}56777 instead of ABCD-56777
Sean Stuber

change

       INNER JOIN PATH p ON f.foldid = p.fldtype || '-' || p.fldkey

to

       INNER JOIN PATH p ON f.foldid = '{' || p.fldtype || '}' || p.fldkey
ASKER
CalmSoul

What happens for instance there is no "FOLDID" for DOC_NAME?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Sean Stuber

If there's no foldid then the join conditions won't pick up those records.
ASKER
CalmSoul

Can we pick up those records?
Sean Stuber

post sample data and expected results for that condition
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
CalmSoul

ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
CalmSoul

Hello sdstuber:

Left join is not doing the trick... Anything else I can try?

thanks
Sean Stuber

It worked for me with the data you provided.

Can you post an example where it doesn't work?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.