You can translate the question and the replies:

Help rewriting REGEXP_LIKE Query to VQL

We are needing some assistance/suggestions in rewriting the following Oracle query. Query is basically creating a list(listagg) of a list of regions gathered from abc.table123 (security config table, each row is different region), and then needs to be compared to a column called regionlist in def.table456. Regexplike is giving us some issues, and believe tochar is as well. Any help would be great, thanks! ``` SELECT * FROM def.table456 WHERE REGEXP_LIKE ( region_list, '(' || (SELECT SUBSTR ( TO_CHAR ( LISTAGG ( CASE WHEN id_value = 'AM' THEN 'AMERICAS' WHEN id_value = 'EU' THEN 'EUROPE' WHEN id_value = 'AS' THEN 'ASIA' WHEN id_value = 'JP' THEN 'JAPAN' WHEN id_value = 'XX' THEN NULL WHEN id_value IS NULL THEN NULL END, '|') WITHIN GROUP (ORDER BY id_value)), 1, 500) region_list FROM abc.table123 WHERE id_type = 'REGION' AND userid = 'user_name') || ')') ```

1 Answer

Hi, For this scenario, I would use the “[Create Base View from Query](https://community.denodo.com/kb/view/document/Using%20the%20Create%20Base%20View%20From%20Query%20Option?category=Data+Sources)” option in the Virtual DataPort Administration Tool to delegate complex functions such as regexp_like entirely into the underlying data source. Further, to concatenate field values, I would use the ‘[GROUP_CONCAT](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/appendix/syntax_of_condition_functions/aggregation_functions#group-concat)’ function in the query which is similar to LISTAGG function. You could take a look at the Knowledge Base article [Oracle SQL to Denodo VQL](https://community.denodo.com/kb/view/document/Oracle%20SQL%20to%20Denodo%20VQL%20Quick%20Reference?category=VQL) for more information. Additionally, if you still need help and if you are a user with valid support access then you can raise a support case in Denodo [Support Site](https://support.denodo.com/) so that our support team can help you. Hope this helps !
Denodo Team
16-04-2021 05:39:36 -0400
You must sign in to add an answer. If you do not have an account, you can register here