Parameters in Fluent BQL

If you need to specify values in a fluent business query language (BQL) statement, you use fluent BQL parameters, which are replaced with the needed values in the translation to SQL. For details about how BQL statements with parameters are translated to SQL, see Translation of a BQL Command with Parameters to an SQL Query Tree.

In this topic, you can find a description of the fluent BQL parameters and the difference between them.

Use of the Current Value of the Field from PXCache

To insert into the SQL query the field value of the Current object from the PXCache object, you append .FromCurrent to the field name in a fluent BQL query. If the Current object from the PXCache object is null, FromCurrent retrieves the default value of the field. If you do not need to retrieve the default value if the Current object is null, you need to append .FromCurrent.NoDefault to the field name in a fluent BQL query. In this case, the system does not retrieve the default value and inserts null.

By using the current field value from PXCache in the declaration of a data view, you can refer to another view to relate these data views to each other. A typical example is referencing the current master record on master-detail forms. For details on how the current field value is used, see To Relate Data Views to Each Another.

Insertion of a Specific Value into the Query

By using these classes, you can pass values to the SQL query, as described in To Pass a Field Value to the SQL Query and To Pass Multiple Field Values to the SQL Query.

Use of the Fluent BQL AsDateTime Parameter Type and Its Variants

Note the difference between the three types of DateTime fields listed in the table in the previous section:

• The fluent BQL parameter types AsDateTime and AsDateTime.UTC represent a DateTime value in Coordinated Universal Time (UTC).
• The parameter type AsDateTime.WithTimeZone represents a DateTime value in the local time zone.

If you want to compare a DateTime field represented by one of these parameter types to a value, then this value must represent the same time zone as the DateTime field that it is being compared to. Suppose that you want to compare a DateTime field of parameter type AsDateTime or AsDateTime.UTC to a value. This value must be converted to the UTC time zone before you execute the query that compares this value to the DateTime field. Conversely, suppose that you want to compare a DateTime field of parameter type AsDateTime.WithTimeZone to a value. This value must be converted to the local time zone before you execute the query that compares this value to the DateTime field.

If the appropriate conversion has not been performed for a value before the value is compared to a DateTime field, as described above, the fluent BQL query may retrieve inaccurate results. Inaccurate results are more likely when the query is using the IsEqual comparison operator.

Use of the Fluent BQL AsString Parameter Type and Its Variants

When you use the fluent BQL parameter type AsString in a fluent BQL query, you should make sure that the your parameter type has the same data type as the underlying field in the database against which this query will be executed. This ensures that the database management system (DBMS) is not forced to perform any type casting to match the field values in this database field to parameter type used in the query. If there is a mismatch between the data type of the database field and the parameter type used in your fluent BQL query, performance may be degraded: The DBMS will cast field values to the fluent BQL parameter type used in the query, and end up having to scan the whole database table instead of being able to use the index that was already built for that field in the database.

An example of such a mismatch occurs when a field in the database has the CHAR data type and you use the generic @P.AsString parameter type in your fluent BQL query to perform a comparison using that database field. The @P.AsString parameter type has the NVARCHAR data type. Thus, the DBMS must cast field values in the database field to NVARCHAR, causing it to be unable to use the index that was already built for this database field based on the original CHAR data type. To mitigate this issue, you should use the appropriate variant of the AsString parameter type that matches the data type of the database field. In the fluent BQL query of the example above, you could mitigate the described issue by using the @P.AsString.ASCII.Fixed parameter type instead of the generic AsString parameter type. The variants of the AsString parameter type are listed in the table of the Insertion of a Specific Value into the Query section.

Insertion of an Optional Value into the Query

To insert an optional value into the query, you append .AsOptional to the field name in a fluent BQL query. If you specify an explicit value for this parameter during the execution of the BQL statement, AsOptional uses the specified value. If you do not specify an explicit value for this parameter during the execution of the BQL statement, AsOptional works similarly to FromCurrent—that is, retrieves the field value of the Current object from the PXCache object and uses the default value of the field if the Current object is null. You can append .AsOptional.NoDefault to the field name in a fluent BQL query to make the system not use the default value and insert null.

By using AsOptional, you can pass the external presentations of the values to the SQL query, as described in To Provide External Presentation of the Field Value to the SQL Query.

When a DAC includes more than one key field, you may need to use .AsOptional in the primary data view of the graph. In this case, the primary data view typically filters the data records by all of the key fields except the last one. For example, you can select documents with the same document type as the current data record has and navigate through these documents with different document numbers. In the following example, the Document DAC has two key fields, DocType and DocNbr.

public SelectFrom<Document>.
        Where<Document.docType.IsEqual<Document.docType.AsOptional>>.View Receipts;

.AsOptional could be replaced with .FromCurrent in the code above unless you need to execute the Receipts data view in code to select a document with specific document type and number.

Insertion of a Value from the UI Control into the Query

By using the Argument classes, you can pass values to the data view delegates. For more information on how to use the Argument classes, see To Pass a Value from a UI Control to a Data View.