This may be a simple solution, however I have been trying to get this to work but unable to.
I am creating a reconciliation app, and trying to limit the load based off a date field.
The date field is 'TransactionEffectiveDate'. In the back end I use this:
FROM [lib://QVD/DTL\TransactionEntry.QVD] (qvd) where TransactionEntrySourceSystemName = 'COGEN' or TransactionEntrySourceSystemName = 'Acturis BE' and date(floor(TransactionEffectiveDate)) >= '01-01-2019'; however, it still loads all dates?
How can I restrict this so that the app only loads where the TransactionEffectiveDate is greater then 01/01/2019? so not loading any dates previous to that date? Thanks in advance
The where clause is evaluated using the rules of boolean algebra. The order of operations for Boolean algebra, from highest to lowest priority is NOT, then AND, then OR. Expressions inside brackets are always evaluated first.
In your case, if TransactionEntrySourceSystemName = 'COGEN' is true, the record will be included regardless of the date. If you write out your expression like this:
true OR false AND false
You can see that evaluating AND first results in your entire expression being true. You can clarify your intent by surrounding the the TransactionEntrySourceSystemName tests with parens:
(true OR false) AND false
or using the match() function as @Kushal_Chawda suggested, effectively combining the two.