advice Question

Oracle sql to return hierarchical result

Avatar of Keith Westberg
Keith Westberg asked on
HTML* Oracle PL/SQLSQLJSONOracle Database
4 Comments1 Solution22 ViewsLast Modified:

I am trying to get my head around how to generate a path or folder structure based on record values.  The order is known, as it’s laid out in the following schema by PARAM_NAME: (PARENT, CONTAINER, PURPOSE, STATUS).  I’m trying to return a result that I can use to generate a folder structure from these values.  But the PARAM_VALUES can be different, meaning a different visual folder structure.  How I do this on the html side is still undecided, but I can’t even consider that part until I can somehow represent the data in a structured form.  Whether it be records via cursor, or a json dump or string.  I just need to be able to walk the data to generate an unordered list or hierarchy.  


Example based on the following data:

Structure

----------------
WORK
   PROJECT
      SPECIAL
         OPEN
            Text.txt
            Other_files…
   TODO
      CHORE
         INPROGRESS
            Image.jpg
            Other_files…
     

Schema

------------------
tbl_files
id   filename
1   text.txt
2   image.jpg


tbl_params
id      file_id   param_name   param_value
100   1          PARENT           WORK
101   1           CONTAINER   PROJECT
102   1           PURPOSE       SPECIAL
103   1           STATUS           OPEN
104   2           PARENT           WORK
105   2           CONTAINER    TODO
106   2           PURPOSE        CHORE
107   2           STATUS            INPROGRESS

Any tips would be greatly appreciated.


Thank you in advance  =]

ASKER CERTIFIED SOLUTION
leakim971
Multitechnician
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 4 Comments.
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
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 4 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004