You can translate the question and the replies:

LIKE is not working as expected

I am running this code on a Denodo database, ``` SELECT TRUNC(tran_dttm) as dt, substr(device, 1, 3) AS tech, SUM(out_qty) AS moves FROM database.table WHERE facility IN '****' and device regexp_like '^P.*' AND tran_dttm >= DATE '2020-09-26' AND tran_dttm <= DATE '2022-09-26' AND tran IN 'W03' or tran IN 'W04' GROUP BY TRUNC(tran_dttm), substr(device, 1, 3) ``` I expect a result that includes only "tech" entrys that start with a "P", but I get results like the following. Date Tech 2020-01-02 00:00:00 L 2022-03-08 00:00:00 P 2021-09-25 00:00:00 P 2022-09-06 00:00:00 P 2021-08-16 00:00:00 L 2021-06-14 00:00:00 L 2021-04-29 00:00:00 P 2021-07-06 00:00:00 P Why am I getting results that do not match the regexp pattern? Am I doing something wrong?
REGEXP_LIKE Like NOT LIKE
user
26-09-2022 12:03:16 -0400
code

1 Answer

Hello, I understand that you are executing a VQL query on a Denodo view in which the results are being filtered using the various conditions in the “WHERE” clause. On looking at the query, it seems that the filter condition after the “OR” clause i.e tran IN 'W04' is causing the unexpected result. Since, if the query is not able to satisfy all the conditions for “AND” clauses, it retrieves the result that satisfies just the “OR” clause condition. It can be resolved by modifying it as either of the following : 1. Putting the “OR” section in parentheses, so that it is isolated from the rest of the “AND” conditions i.e ``` WHERE facility IN '****' and device regexp_like '^P.*' AND tran_dttm >= DATE '2020-09-26' AND tran_dttm <= DATE '2022-09-26' AND (tran IN 'W03' or tran IN 'W04') ``` 1. You can rewrite the expression `tran IN 'W03' or tran IN 'W04'` as : `tran IN 'W03,W04'` “IN” can match from the list of values on the right side. If the issue still persists, I would recommend executing the query incrementally. For example, execute it with just one WHERE condition and check if it gives expected result. And then, add another condition to the WHERE clause. Keep repeating this until you reach the stage where the results are not correct. This will provide the root cause of the incorrect results and accordingly you can re-work on that part of the query. Hope this helps!
Denodo Team
27-09-2022 12:11:48 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here