Just wanted to answer a few of these.
Regarding ISNULL vs = “” (double quotes) or '' (single quotes): With the latter, what we’re looking for is empty strings in a field that exists. What we’re searching for with ISNULL is records that do not exist. For example, in the below BAQ, I’m looking for OrderDtl records that are not on a shipment/PackList:
``
Alternatively, if I change the PackNum to '' (BAQ replaces this with “Empty string”), I get an error, because PackNum is not an nvarchar type field:
``
If I change that to a varchar field, like ShipDtl.LineDesc, I don’t get an error, but I also don’t get any rows returned. All those rows where PackNum is null are not returned, becuase LineDesc is also null, not an empty string:
``
Regarding LIKE, MATCHES, EXISTS, BEGINS, etc, I don’t really use MATCHES so I can’t speak to that. For the others, you just need to know the Syntax. In the below query, I’m searching for OrderDtl lines that aren’t on a shipment, where the PartNum begins with “DC”. In SQL, I would use OrderDtl.PartNum LIKE 'DC%'. In a BAQ, I can use the below SupQuery (or Table) Criteria. No % required:
``
However, with LIKE you do have to use the % wildcard:
``
For EXISTS, that’s used when you have another subquery, and you want to check if your field value exists in a specified field of the other subquery.