| Accessing Data | 136
9. In each Where or On clause, replace each comparison with the corresponding comparison
operator, such as Equal, Greater, or IsNull. For more information on constructing comparisons,
see To Filter Records.
The following sample code includes these changes (shown in bold type).
PXSelectJoin<Product,
InnerJoin<SupplierProduct,
On<SupplierProduct.productID, Equal<Product.productID>>,
InnerJoin<Supplier,
On<Supplier.accountID, Equal<SupplierProduct.accountID>>>>,
Where2<Where<Product.bookedQty, IsNotNull,
And<Product.availQty, IsNotNull,
And<Product.minAvailQty, IsNotNull,
And2<Where<Product.active, Equal<True>,
Or<Product.active, IsNull>>,
And<Where<Product.bookedQty, Greater<Product.availQty>,
Or<Product.availQty, Less<Product.minAvailQty>>>>>>>>,
Or<Product.availQty, IsNotNull>>,
OrderBy<Asc<Product.unitPrice, Desc<Product.availQty>>>>
10. Align logical operators of the same level so that they have the same indentation and so that
each simple condition is placed on a separate line. Do not add line breaks before nested Where
clauses.
11. If your SQL statement includes the GROUP BY clause, do the following:
a. Replace the GROUP BY clause with the Aggregate clause.
b. Chain the GroupBy clause and aggregation functions (such as Min, Max, Sum, Avg, and
Count) to one another as described in To Group and Aggregate Records.
12. If your SQL statement includes the ORDER BY clause, do the following:
a. Replace the ORDER BY clause with the OrderBy clause. The following sample code shows
this change (with changes shown in bold type).
PXSelectJoin<Product,
InnerJoin<SupplierProduct,
On<SupplierProduct.productID, Equal<Product.productID>>,
InnerJoin<Supplier,
On<Supplier.accountID, Equal<SupplierProduct.accountID>>>>,
Where2<Where<Product.bookedQty, IsNotNull,
And<Product.availQty, IsNotNull,
And<Product.minAvailQty, IsNotNull,
And2<Where<Product.active, Equal<True>,
Or<Product.active, IsNull>>,
And<Where<Product.bookedQty, Greater<Product.availQty>,
Or<Product.availQty, Less<Product.minAvailQty>>>>>>>>,
Or<Product.availQty, IsNotNull>>,
OrderBy<Product.unitPrice, Product.availQty DESC>>
b. Chain the Asc and Desc operators to one another, as described in To Order Records. The
following sample code shows this change (with changes shown in bold type).
PXSelectJoin<Product,
InnerJoin<SupplierProduct,
On<SupplierProduct.productID, Equal<Product.productID>>,
InnerJoin<Supplier,
On<Supplier.accountID, Equal<SupplierProduct.accountID>>>>,
Where2<Where<Product.bookedQty, IsNotNull,
And<Product.availQty, IsNotNull,
And<Product.minAvailQty, IsNotNull,
And2<Where<Product.active, Equal<True>,
Or<Product.active, IsNull>>,
And<Where<Product.bookedQty, Greater<Product.availQty>,