Clarity

Expand all | Collapse all

SQL query for derived tables in a custom domain (advanced reporting)

  • 1.  SQL query for derived tables in a custom domain (advanced reporting)

    Posted 04-10-2017 11:13 AM

    Hi guys, we've built our own custom domain in advanced reporting and within this have incorporated our own SQL to build derived tables. We're struggling with how to extract data from an 'Ideas' table, and from a custom column in particular that stores multiple values. This column stores multiple values as a single string separated by semicolon (;), and we cant quite find the best functions to extract the individual values in PL SQL (Oracle). SPLIT couldn't be recognised and SUBSTR worked but could become a fairly complex query. 

    Here's an example to illustrate:
    We created a column called Products where the user can multi-select and find values as follows in the database table:
    prod_a;prod_b;prod_c

    prod_a;prod_c

    prod_a

    prod_b;prod_c

    etc

     

    Any pointers would greatly help. 

    Thanks!



  • 2.  Re: SQL query for derived tables in a custom domain (advanced reporting)

    Posted 04-19-2017 06:58 AM

    regexp_substr function can be used for this. 

    Following link can also be referred :

    How to split comma separated string and pass to IN clause of select statement (ADF Illusions)