AnsweredAssumed Answered

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

Question asked by ANAK on Apr 10, 2017
Latest reply on Apr 19, 2017 by Kritika Rana

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!

Outcomes