SQL Queries with LIKE on dyn_*
Posted: Mon Jun 28, 2021 3:03 pm
Hello,
I have an archived dyn_int datapoint and would like to use the WHERE statement to filter it upfront a request. I know the data is valid if the length is for example 7. My Idea was, i could use the the LIKE statement combined with wildcards. I got a working proof of concept query:
SELECT '_offline.._value', '_offline.._stime' FROM 'my_dp' WHERE '_offline.._value' LIKE \"? | ? | ? | ? | ? | ? | ?\" TIMERANGE(\"2021.05.01 00:00:00.000\",\"2021.06.30 00:00:00.000\",1,0)
It gives me all values with length of 7. If I want all data having a 16 on index 2 I would expect just replacing the second question mark. This gives me as well the expected result: All data containing a 16 at the second index inside my timerange.
But when I want to filter after the first index containing a 1, the result is empty, but there is data.
Is this an unexpected behavior or am I using it wrong? I am using WinCC OA v3.16 P27.
Best Regards
I have an archived dyn_int datapoint and would like to use the WHERE statement to filter it upfront a request. I know the data is valid if the length is for example 7. My Idea was, i could use the the LIKE statement combined with wildcards. I got a working proof of concept query:
SELECT '_offline.._value', '_offline.._stime' FROM 'my_dp' WHERE '_offline.._value' LIKE \"? | ? | ? | ? | ? | ? | ?\" TIMERANGE(\"2021.05.01 00:00:00.000\",\"2021.06.30 00:00:00.000\",1,0)
It gives me all values with length of 7. If I want all data having a 16 on index 2 I would expect just replacing the second question mark. This gives me as well the expected result: All data containing a 16 at the second index inside my timerange.
But when I want to filter after the first index containing a 1, the result is empty, but there is data.
Is this an unexpected behavior or am I using it wrong? I am using WinCC OA v3.16 P27.
Best Regards