To Select Records By Using Traditional BQL

To select records from the database, you can construct a business query language (BQL) statement. To construct a BQL statement, you use one of the generic PXSelect classes. You select the needed PXSelect class depending on the statement you need to compose, as described in the sections of this topic.

Note:

In a PXSelect class, you configure a query to the database. The actual request to the database is performed once you cast the result of the query execution to a DAC or an array of DACs, or iterate through DACs in the result with the foreach statement. For details, see Data Query Execution.

Before You Proceed

Make sure that the application database has the database tables from which you are going to request data, and that the application defines the data access classes (DACs) for these tables. For more information on defining DACs, see Data Access Classes in Traditional BQL.

To Select All Records from a Database Table

To select all data from one database table without applying any filtering conditions or ordering, use one of the PXSelect classes that has DAC as the only type parameter, such as the PXSelect<Table> or PXSelectReadonly<Table> class, as shown in the following sample BQL statement.

PXSelect<Product>

In this BQL statement, you are selecting all data records (with the values of all bound fields) from the Product table.

Note:

For example, suppose that the Product table has two columns, ProductID and UnitPrice. In this case, MYOB Acumatica Framework translates the previous BQL statement to the following SQL query. The framework adds ordering by the DAC key field (in ascending order) to the end of the SQL query because the BQL statement does not specify ordering.

SELECT Product.ProductID, Product.UnitPrice FROM Product 
       ORDERBY Product.ProductID

To Filter Records

To filter records in the database table to be retrieved, construct a BQL statement with conditions by doing the following:

Use one of the PXSelect classes that has the Where type parameter, such as PXSelect<Table, Where>. For the full list of PXSelect classes, see PXSelect Classes.
Specify the filtering conditions by using the Where clause, as described in To Filter Records.
To specify the fields that should be used for filtering, use the class fields defined in the DACs, such as Product.productID. (The name of the field class starts with a lowercase letter. Do not confuse it with the property field, which has the same name but starts with uppercase letter.)

The following sample BQL statement selects all data records from the Product table that have the specified value in the ProductID column.

PXSelect<Product,
    Where<Product.productID, Equal<Required<Product.productID>>>>

Note:

MYOB Acumatica Framework translates the previous BQL statement to the following SQL query. In this SQL query, [list of columns] is the list of columns of the Product table; [parameter] is the value passed to the Select() method of the PXSelect class, which is called when the BQL query is executed.

SET @P0 = [parameter];

SELECT [list of columns] FROM Product
    WHERE Product.ProductID = @P0
        ORDERBY Product.ProductID

MYOB Acumatica Framework explicitly enumerates the columns of the database table in the SQL query. For details on which columns are enumerated, see Translation of a BQL Command to SQL.

To Order Records

To order records in the database table to be retrieved, construct a BQL statement with ordering by doing the following:

Use one of the PXSelect classes that has the OrderBy type parameter, such as PXSelectOrderBy<Table, OrderBy> or PXSelect<Table, Where, OrderBy>. For the full list of PXSelect classes, see PXSelect Classes.
Use the OrderBy clause to order records, as described in To Order Records.
To specify the field that should be used for filtering, use the class field defined in the DAC, such as Product.productID. (The name of the field class starts with a lowercase letter. Do not confuse it with the property field, which has the same name but starts with uppercase letter.)

The following sample BQL statement selects all Product data records and sorts them by the UnitPrice field in ascending order.

PXSelectOrderBy<Product, OrderBy<Asc<Product.unitPrice>>>

Note:

MYOB Acumatica Framework translates the previous BQL statement to the following SQL query, where

[list of
          columns]

is the list of columns of the Product table.

SELECT [list of columns] FROM Product
    ORDER BY Product.UnitPrice

MYOB Acumatica Framework explicitly enumerates the columns of the database table in the SQL query. For details on which columns are enumerated, see Translation of a BQL Command to SQL.

To Query Multiple Tables

To join multiple tables, construct a BQL statement by doing the following:

Use one of the PXSelect classes that has the Join type parameter, such as PXSelectJoin<Table, Join> or PXSelectReadonly2<Table, Join>.
In the Join type parameter of the PXSelect class, use one of the Join clauses—such as InnerJoin, LeftJoin, RightJoin, FullJoin, or CrossJoin—that are directly mapped to SQL JOIN clauses, as shown in the following sample BQL statement. For more information on the use of Join clauses, see To Query Multiple Tables.
```
PXSelectJoin<SalesOrder,
    InnerJoin<OrderDetail,
        On<OrderDetail.orderNbr, Equal<SalesOrder.orderNbr>>>>
```
Note:
MYOB Acumatica Framework translates the previous BQL statement to the following SQL query, where [list of columns] is the list of columns of the joined tables.
```
SELECT [list of columns] FROM SalesOrder
INNER JOIN OrderDetail
    ON OrderDetail.OrderNbr = SalesOrder.OrderNbr
```
MYOB Acumatica Framework explicitly enumerates the columns of the database table in the SQL query. For details on which columns are enumerated, see Translation of a BQL Command to SQL.

To Aggregate Records

To group or aggregate records, construct a BQL statement by doing the following:

Use one of the PXSelect classes with the Aggregate type parameter, such as PXSelectGroupBy<Table, Aggregate>.
In the Aggregate type parameter of the PXSelect class, specify the grouping conditions and aggregation functions by using the Aggregate<Function> class, the GroupBy clauses, and the Min, Max, Sum, Avg, and Count aggregation functions, as shown in the following sample BQL statement. For more information on the use of the grouping conditions and aggregation functions, see To Group and Aggregate Records in Traditional BQL.
```
PXSelectGroupBy<Product,
    Aggregate<GroupBy<Product.categoryCD>>>
```
Note:
MYOB Acumatica Framework translates the previous BQL statement to the following SQL query.
```
SELECT Product.CategoryCD,
       [MAX(Field) for other fields]
FROM Product
GROUP BY Product.CategoryCD
```