Skip to main content
Solved

Malfunctioning of Acumatica FBQL SubSelect


aaghaei
Captain II
Forum|alt.badge.img+9

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?

 

@Dmitrii Naumov @Yuriy Zaletskyy @smarenich 

Best answer by JKurtz29

For your queries, you should be selecting BAccountID, not DefPOAddressID, so that you return a list of account id’s.

 

//#1 SELECT * FROM Vendor WHERE BAccountID NOT IN (SELECT BAccountID FROM BAccount WHERE DefPOAddressID IS NULL)

View original
Did this topic help you find an answer to your question?

2 replies

JKurtz29
Jr Varsity III
Forum|alt.badge.img
  • Jr Varsity III
  • 15 replies
  • Answer
  • November 9, 2023

For your queries, you should be selecting BAccountID, not DefPOAddressID, so that you return a list of account id’s.

 

//#1 SELECT * FROM Vendor WHERE BAccountID NOT IN (SELECT BAccountID FROM BAccount WHERE DefPOAddressID IS NULL)


aaghaei
Captain II
Forum|alt.badge.img+9
  • Author
  • Captain II
  • 1180 replies
  • November 10, 2023

@JKurtz29 Your comment is valid. A few months ago when I created this post I figured it out I had a brain freez. 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings