Hello All,
If I am not mistaken, Acumatica introduced “IsInSubselect” & “IsNotInSubselect” with FBQL which is the Equivalent of “IN” and “NOT IN” of SQL. When we use these two functions it is translated to SQL correctly but they have a deadly flaw. This flaw impacts the whole result and when you expect to have thousands of records you get NONE. Look at the two below SQL queries (just logic, do not think what a stupid query). Please note I can NOT use Join
//#1
SELECT * FROM Vendor WHERE BAccountID NOT IN (SELECT DefPOAddressID FROM BAccount WHERE DefPOAddressID IS NULL)
//#2
SELECT * FROM Vendor WHERE BAccountID NOT IN (SELECT ISNULL(DefPOAddressID, 0) FROM BAccount WHERE DefPOAddressID IS NULL)
Both queries are instructing SQL to return all vendors which do not have DefPOAddressID but the first query returns none while the second one in my case over 2,000 and this is what I expect to get. The only difference is the ISNULL wrapping. Acumatica translates its queries to the first one so I get no result but I should. I tried to append my FBQL query by IfNullThen
.SearchFor<BAccount.defPOAddressID.IfNullThen<int0>>
but I get an error in the above part that can not be converted to IBqlField. Does anyone have a workaround or have come across this issue?