Problems with the BQL "IN<>" statement

The requirement I have is to get a list of all discount codes defined in an instance and which ones a particular customer is currently assigned to, in the case given CustomerID=28. I further have to include only discount codes that naturally will be applicable to customers. There are only 3 of these; "Customer", "Customer and Item", "Customer and Item price Class". These are ARDiscount.ApplicableTo containing "CU", "CP","CI"

Select a.CompanyID, a.DiscountID, a.DiscountSequenceID, b.ApplicableTo, c.CustomerID
From   DiscountSequence a
  Join ARDiscount b On a.CompanyID = b.CompanyID and a.DiscountID = b.DiscountID
  Left Outer Join DiscountCustomer c On a.CompanyID = c.CompanyID 
                                      And a.DiscountID = c.DiscountID 
                                      And a.DiscountSequenceID = c.DiscountSequenceID 
                                      And (IsNull(c.CustomerID,0) = 0 OR c.CustomerID = 72)
Where  a.CompanyID = 2
  And  b.ApplicableTo In ('CU','CP','CI')
Order By a.DiscountID, a.DiscountSequenceID

I created data view delegate to return the 4 columns I need to display and in the view I created to read the data like the SQL query above I used the BQL "IN<>" statement like this. The method was taken directlty from a blog post found here : https://asiablog.acumatica.com/2017/11/sql-in-operator-in-bql.html

Object[] applicableTovalues = new String[] { "CP","CI","CU" };   // Customer and Price Class // Customer and Item// Customer

var Results = PXSelectJoin<DiscountSequence
            , InnerJoin<ARDiscount, On<DiscountSequence.discountID, Equal<ARDiscount.discountID>>
            , LeftJoin<DiscountCustomer, On<DiscountSequence.discountID, Equal<DiscountCustomer.discountID>,
                                        And<DiscountSequence.discountSequenceID, Equal<DiscountCustomer.discountSequenceID>,
                                        And<Where<DiscountCustomer.customerID, Equal<Current<Customer.bAccountID>>,
                                               Or<DiscountCustomer.customerID, IsNull>>>>>>>
                , Where<DiscountSequence.discountID, IsNotNull
                , And<ARDiscount.applicableTo, In<Required<ARDiscount.applicableTo>>>>
                , OrderBy<Asc<DiscountSequence.discountID, Asc<DiscountSequence.discountSequenceID>>>
                >.Select(Base, applicableTovalues);

The problem is that the resulting SQL server select statement caught with TRACE only includes the first of the three IN values (''CU'') leaving (CI and CU) out. I was expecting all three values in the IN statement like this : AND [ARDiscount].[ApplicableTo] IN ( ''CP'', ''CI'', ''CU'')

exec sp_executesql N'SELECT [DiscountSequence].[DiscountID], [DiscountSequence].[DiscountSequenceID], [DiscountSequence].[LineCntr], 
<snip>
[DiscountCustomer].[CreatedDateTime], [DiscountCustomer].[LastModifiedByID], [DiscountCustomer].[LastModifiedByScreenID], [DiscountCustomer].[LastModifiedDateTime]  
FROM [DiscountSequence] [DiscountSequence]  INNER JOIN [ARDiscount] [ARDiscount] ON ( [ARDiscount].[CompanyID] = 2) AND [DiscountSequence].[DiscountID] = [ARDiscount].[DiscountID]  
LEFT JOIN [DiscountCustomer] [DiscountCustomer] ON ( [DiscountCustomer].[CompanyID] = 2) AND [DiscountSequence].[DiscountID] = [DiscountCustomer].[DiscountID] 
AND [DiscountSequence].[DiscountSequenceID] = [DiscountCustomer].[DiscountSequenceID] AND ( [DiscountCustomer].[CustomerID] = @P0 OR [DiscountCustomer].[CustomerID] IS NULL )  
WHERE ( [DiscountSequence].[CompanyID] = 2) 
AND ( [DiscountSequence].[DiscountID] IS NOT NULL  
AND [ARDiscount].[ApplicableTo] IN ( ''CU''))  
ORDER BY [DiscountSequence].[DiscountID], [DiscountSequence].[DiscountSequenceID] 
OPTION(OPTIMIZE FOR UNKNOWN) /* AR.30.30.00 */',N'@P0 int',@P0=39


Read more here: https://stackoverflow.com/questions/67943936/problems-with-the-bql-in-statement

Content Attribution

This content was originally published by Hank Vanderstoep at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: