Inline Query | SQL like Queries in Business Central

Updated: Nov 20

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

© 2020 by msnJournals.com

  • Facebook Social Icon
  • LinkedIn Social Icon
  • Twitter Social Icon
  • GitHub-Mark-120px-plus