You are here: Using BML > Functions & Scripts > BigMachines Query Language (BMQL) Overview

BigMachines Query Language (BMQL) Overview

OVERVIEW

CPQ Cloud can query system tables and user-created Data Tables from within BML by using a SQL-like syntax.

This functionality has replaced the deprecated gettabledata() and getpartsdata() functions. For more information on these and other database functions, see Direct DB Access.

BMQL is a function that contains the results of an SQL query. Click here for more information on SQL.

Highlights:

ADMINISTRATION

ClosedBasics

Syntax: bmql(sqlQuery [, stringDict])

Parameters:

# Parameter Data Type Description
1 sqlQuery String The SQL query
2 [dict] String, Integer, or Float Optional: A dictionary populated with the language context variable as a key and an overriding language as the value.

Return Type: Record Set

This data type is a collection of dictionaries. It can also be used as a function in conjunction with BMQL.

Example of bmql():

rs = recordset();

if(...) {

rs = bmql(query1);

}

else {

rs = bmql(query2);

}

return rs;


ClosedAdvanced

Closedbmql(sqlQuery)

ClosedStatement Keywords

ClosedThe DISTINCT Keyword


ClosedWHERE Clauses and Parameters

ClosedOverview

ClosedWHERE Condition
ClosedConditional Evaluation

ClosedTransaction Data

You can access Commerce transaction data, from both main documents and sub-documents, in advanced functions within Configuration.

This functionality is not supported outside of Configuration.

Data Available using BMQL Transaction:

The BMQL Transaction function is context-sensitive.


ClosedOperators

User operators to refine your queries. There are two kinds of operators:

Operator Description Example
LIKE This is used as "contains". SELECT part_number FROM _parts WHERE part_number LIKE $var_1
NOT LIKE This is used as "does not contain". SELECT part_number FROM _parts WHERE part_number IS NOT LIKE $var_1
IN This is used to find a value within an array. SELECT part_number FROM _parts WHERE part_number LIKE $var_1 IN $lead_time
NOT IN
This is used to find values outside of an array. SELECT part_number FROM _parts WHERE part_number LIKE $var_1 NOT IN $lead_time
IS NULL This is used to find null values in an array. SELECT part_number FROM _parts WHERE part_number LIKE $var_1 IS NULL
IS NOT NULL This is used to find values that are not null in an array. SELECT part_number FROM _parts WHERE part_number LIKE $var_1 IS NOT NULL

ClosedWildcards

ClosedFinding Available Tables

Within each Function Wizard, scroll down to the end of the Behavior section to find a link that opens a pop-up window that will show ALL accessible databases and Data Tables. Click the database or Data Table name to see a list of column names and whether a column can be used in the WHERE clause.

BMQL does not support a parts query that retrieves more than 500 parts from a non-default Price Book.

The BMQL query is parsed at runtime.

OrderBy and Distinct cannot be grouped together using AND or OR.

For security reasons, the query cannot be built dynamically and passed into BMQL. Dynamic values can be passed in the WHERE clause of the query by preceding the variable name with a "$".


ClosedParameters

ClosedcontextOverride

This dictionary can be populated with the language context variable as a key and an overriding language as the value. When pulling data from the _parts Data Table, this will substitute this language in place of the user's preferred language.

For a complete list of supported languages and their corresponding codes, see Language Support.

If the value for the requested language is blank, the site base language will be used instead. Additionally, if the requested language is not enabled or not found, the context variable will be ignored.

Example:

lang = dict("string");

put(lang, "language", "de");

results = bmql("select description from _parts where part_number = ‘Translations’", lang);

This will return the German description of the Translations part.

This parameter is optional.

ClosedfieldMap

The fieldMap parameter is a string dictionary. It is used when the WHERE clause has been completely substituted with a string variable, and there are also variables within the WHERE clause.

In this case, each variable in the dynamic WHERE clause must be passed into the fieldMap dictionary and referenced by its key in the WHERE clause. All variables must be passed as string types, regardless of their data type in the Data Table.

Example:

lang = dict(“string”);

fields = dict("string");

x_var = "6.08";

put(fields, "$field1", x_var);

where = "float1 = $field1";

results = bmql("select columnName from tableName WHERE $where", lang, fields);

This parameter is optional.

If the third parameter is used, the second parameter must also be defined.


USE CASE EXAMPLES

For all of the use cases below, we'll be using a user created Data Table named "sammie". Click a Sample Use Case below to see an example.

ClosedSample Use Case 1: Using BMQL function

In this example, let's say you'd like to run a query to return the Price and Type from the "sammie" table shown above.

  1. Using the information from the "sammie" Data Table, create a new script using BMQL that will return Price and Type. Click the checkboxes in the Select column to select your columns and From to select your database object.
  2. Create a for...loop to loop through your record set created using the BMQL function.
  3. In this example, we are using the print statement to show the results of the query.

Now, you can compare the results of the query to what is on the table and see that it pulled the correct Type and Price from the table.

In this example, we are using the same sample case, but adding the "distinct" keyword. Remember, using distinct will only return distinct values, essentially removing any duplicates.

As you can see, where there were multiple sets that were the same in the first example, now those have been removed.


ClosedSample Use Case 2: Using the get function and the WHERE Clause

This example takes Sample Use Case 1 a step further by adding a WHERE clause to the select statement. We also add the get() function.

  1. Using the information from the "sammie" Data Table, create a new script using BMQL that will return Part, Description and Price when the Type in the Data Table is "Hot". Click the checkboxes in the Select column to select your columns and From to select your database object.
  2. Create a for...loop to go through your record set created using the BMQL function.
  3. Use the get() function with record being used like a dictionary and Price being the data to be returned.

As you can see below, "record" acts like a dictionary and returns the columns and data you requested. You should also notice the get() function at work, returning the price for each of the records.


ClosedSample Use Case 3: Using a WHERE condition

In this example, we are going to add a condition. So, the first screen show we'll say that if the condition is true, that the predicate will query for the part number "HS001". When adding a WHERE condition, the syntax is $eval AND field = value. In this case, we are searching for part number "HS001" when the condition is True.

In the console, you'll notice that when the part number is "HS001", the Price and Type have been returned. We'll then evaluate what happens if the condition is False:

In the console, you'll notice that when the condition is False, the predicate (Part = 'HS001') is ignored and all other results are returned.


ClosedSample Use Case 4: Errors

The user can retrieve errors or warnings using the getMessage function.


ClosedSample Use Case 5: Recommended Item Rule


ClosedSample Use Case 6: Using BMQL Transaction

In this example, we'll show you how to return commerce transaction data back to configuration. The attribute Opportunity Name has been set with the value Toni's Pizza.

This was set through a recommendation rule using BMQL Transaction. Notice the use of commerce.quote_process after the FROM statement. This is the variable name of the quote document from where you're querying data. You can also use the variable name of the sub-document (for example, line_process).

The WHERE clause is not accepted when you are returning commerce data to configuration.


NOTES

    NULL and blank Integer values are treated as separate values.

    - NULL= 0
    - Blank = ""

    If you use logic that tests for NULL values in rule conditions or BML, this logic should be updated.

    Using "null" as an attribute value is strongly discouraged.

RELATED TOPICS

Related Topics Link IconSee Also