filtered by the DB in SQL ??

xavier

New member
In an anatella In the firstnode, there is the condition that the TARGETID is > 1000

However, when I put a filter below, there are still TARGETIDs with values below 1000.

xavier forum 3.png

We don't understand why it is a numeric field.
 

Support

Administrator
Hello Xavier!

It's actually quite destabilizing as a situation!

The SQL command which contains the "TARGETID>1000" is executed by your database (and not by Anatella). In fact, Anatella just transmits "as is" the text of the SQL command to the database without understanding it and it is the database that understands the command and executes it correctly. Then, Anatella retrieves just a few lines of output from the database. So, in this case (i.e. every time there is a SQL command to be executed), to better understand the error, you should refer to the database user manual.

That being said, I wonder if the error is not due to a lack of parentheses?
For example, by adding just a few parentheses in your "where" clause, we could get this:
... WHERE (TECHCREATIONDATE>'2019-01-01' AND TYPE=1)
GOLD TYPE=16
OR ((TYPE=17) AND TARGETID>1000)

...and the above SQL query retrieves all rows with TYPE=16, even if the row has TARGETID < 1000.

So to solve the problem I would be more precise with the brackets and write:
... WHERE TECHCREATIONDATE>'2019-01-01'
AND (TYPE=1 OR TYPE=16 OR TYPE=17)
AND TARGETID>1000

That should fix the problem! (unless there is a bug in the database).
 
Top