You can translate the question and the replies:

Using Regexp_like in Case in Select

Question about CASE in the SELECT statement of query. I have this in Oracle. CASE WHEN REGEXP_LIKE (UPPER (MAIN_PRODUCT), 'CBMCLS|CBMOT|CBMSHG') THEN REGEXP_REPLACE (UPPER (MAIN_PRODUCT), 'CBMCLS|CBMOT|CBMSHG', 'CBM') ELSE UPPER(MAIN_PRODUCT) END AS PROD_GROUP How do I convert this in to a valid VQL condition? Please advise. Thanks.
REGEXP_LIKE CASE Select Query
user
25-09-2018 16:09:50 -0400
code

2 Answers

Hi, The [regexp](https://community.denodo.com/kb/view/document/Oracle%20SQL%20to%20Denodo%20VQL%20Quick%20Reference?category=VQL) function similar to regexp_replace, and like keyword are available in denodo for you to use, and I believe are well suited to what you’re doing. For the regexp_like, replacing it with 1 or 3 like statements is simple enough CASE WHEN upper(MAIN_PRODUCT) like ‘CBM%’’ THEN… or CASE WHEN upper(MAIN_PRODUCT) like ‘CBMCLS’ OR upper(MAIN_PRODUCT) like ‘CBMOT’ OR ... THEN ... For regexp_replace, see the above documentation. Hope this helps!
Denodo Team
26-09-2018 17:09:07 -0400
code
Did more test. Looks like the regexp expression used in Oracle work just fine in here. Here is what works for mine. case WHEN (upper(main_product) regexp_like 'CBMCLS|CBMOT|CBMSHG') THEN regexp(upper(main_product), 'CBMCLS|CBMOT|CBMSHG', 'CBM') end Hope that helps others.
user
27-09-2018 11:26:31 -0400
You must sign in to add an answer. If you do not have an account, you can register here