Inline Query | SQL like Queries in Business Central
Updated: Nov 20, 2020
Inline Query is a library that can execute SQL like Queries in Business Central AL Language. This is a small compiler in AL that compiles and executes SQL like queries in text constants or text variables.
Query Object vs Inline Query
Inline Query is not a replacement for Query Objects. At present, Inline Query can only support retrieval of a single column from a single table (joins are not supported). Whereas Query Object supports multiple tables and multiple columns.
Why Inline Query?
I (MSN Raju) have created this library just to try something new. Actually, there is no purpose when I started writing this, but now I can give some use cases.
The following are the benefits:
Dynamic - Inline Queries can compose and run at the runtime.
No Record Variables - No need to declare record variables, it is just a simple Text Query.
Readability - SQL is like simple English and it is easy to read. Example: select sum(Amount) from [G/L Entry] where [No.] = 'CASH' and [Document Type] = 'Payment'. Anybody can understand what this line is about.
No of Lines - To write the same above query in AL, at least 3 lines of code should be written. Whereas, for Inline Query, it is a single line statement.
Limitations / Disadvantages:
It compiles the Query Text every time before executing. This is not good for performance.
FlowFields, multiple columns, and ORDER BY are not supported.
Only SELECT queries are supported.
Query Syntax
SELECT [TOP n] FUNCTION(<Field Name>) | <Field Name> [AS <Column Name>] FROM [<Company Name>.]<Table Name> [ WHERE <Field Name> OPERATOR <Filter Value> [ AND <Field Name> OPERATOR <Filter Value>] ]
Company Name
Name of the company from which data is to be retrieved, and this is optional. If there are any spaces or special characters in the company name, then it should be enclosed with brackets.
Table Name
Name of the table from which data is to be retrieved. If there are any spaces or special characters in the table name, then it should be enclosed with brackets.
Field Name
Name of the field in the source table. If there are any spaces or special characters in the field name, then it should be enclosed with brackets.
FUNCTION (Aggregate Function)
The following aggregate functions are supported in Inline Quaries:
Count - To get a no. of records in the table after applying filters
Min - To get minimum value of the field in the table after applying filters
Max - To get maximum value of the field in the table after applying filters
Avg - To get average value of the field in the table after applying filters
Sum - To get sum of the field values in the table after applying filters
First - To get the first value of the field in the table after applying filters
Last - To get the last value of the field in the table after applying filters
OPERATOR
The following operators are supported in Inline Queries:
Equal to: =
Less than: <
Less than or equal to: <=
Greater than: >
Greater than or equal to: >=
Not equal to: <>
Like: LIKE
Note: For LIKE - Field Filter expressions are accepted as Filter Value. (Internally uses SetFilter statement)
Filter Value
The following are the possible Filter Values:
BOOLEAN: true or false can be use as filter value to apply filter on Boolean field.
NUMBER: Integer and Decimal values are accepted as filter values to apply filter on Number type fields.
TEXT CONST: Text constant values should always be wrapped with single quotes. Text Constant should be used to apply filter on Text, Code, Date, Time, DateTime, Option, Enum type fields.
Filter Value for LIKE operator should be a Text Constant and Filter Value should be in regional format for Date, Time and DateTime fields.
Inline Query Codeunit
This codeunit contains methods that can execute Inline Queries. It has the following methods:
AsInteger
Input: Query Text
Returns: Integer vaue
AsDecimal
Input: Query Text
Returns: Decimal value.
AsBigInteger
Input: Query Text
Returns: BigInteger value.
AsDate
Input: Query Text
Returns: Date value
AsTime
Input: QueryText
Returns: Time value.
AsDateTime -
Input: Query Text
Returns: DateTime value.
AsBoolean -
Input: Query Text
Returns: Boolean value.
AsText
Input: Query Text
Returns: Text value.
AsCode
Input: Query Text
Returns: Boolean value.
AsJsonArray
Input: Query Text
Returns: JsonArray
Note: SUM and AVG aggregate functions are not applicable to AsDate, AsTime and AsDateTime methods. Also, FIRST and LAST aggregate functions are the only functions that can be used in AsBoolean, AsText and AsCode methods.
Examples
1) Count of released Sales Orders
Query
SELECT COUNT(1) FROM [Sales Header] WHERE Status = 'Released'AL Code
procedure GetOrderCount(): Integer
var
InlineQuery: Codeunit "Inline Query";
OrderCount: Integer;
QueryTxt: Label 'SELECT COUNT(1) FROM [Sales Header] WHERE Status = ''Released''', Locked = true;
begin
OrderCount := InlineQuery.AsInteger(QueryTxt);
exit(OrderCount);
end;2) Sales Order's total "Amount including VAT" for a particular order.
Query
SELECT SUM([Amount Including VAT]) FROM [Sales Line] WHERE [Document Type] = 'Order' AND [Document No.]='ORD001'AL Code
local procedure GetOrderAmount(): Decimal
var
InlineQuery: Codeunit "Inline Query";
OrderAmount: Integer;
QueryTxt: Label 'SELECT SUM([Amount Including VAT]) FROM [Sales Line] WHERE [Document Type] = ''Order'' AND [Document No.] = ''ORD001''', Locked = true;
begin
OrderAmount := InlineQuery.AsDecimal(QueryTxt);
exit(OrderAmount);
end;3) Average Order value per Sales Line:
Query
SELECT AVG([Amount Including VAT]) FROM [Sales Line] WHERE [Document Type] LIKE 'Order|Invoice'AL Code
local procedure GetAverageValue(): Decimal
var
InlineQuery: Codeunit "Inline Query";
OrderAmount: Integer;
QueryTxt: Label 'SELECT AVG([Amount Including VAT]) FROM [Sales Line] WHERE [Document Type] LIKE ''Order|Invoice''', Locked = true;
begin
OrderAmount := InlineQuery.AsDecimal(QueryTxt);
exit(OrderAmount);
end;Conclusion
This is an example on how AL Language can be extended. Though I have written tests for this library with 90%+ code coverage, but still It has to go through tough testing for production use. And most importantly compiled queries should be cached for better performance. I will implement the same in the next version.
You can find source code at GitHub.
Happy Coding!!!
#MsDyn365 #MsDyn365BC #BusinessCentral #DynamicsNAV #InlineQuery