msnraju / inline-query Goto Github PK

View Code? Open in Web Editor NEW

22.0 1.0 9.0 80 KB

Inline Query | SQL like Queries in Business Central

Home Page: https://www.msnjournals.com/post/inline-query-sql-like-queries-in-business-central

License: MIT License

AL 95.96% CSS 1.15% JavaScript 2.88%

al-language business-central sql-query dynamics-365

inline-query's Introduction

Inline Query | SQL like Queries in Business Central

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.

Example

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;

See msnJournals.com for more information.

inline-query's People

Contributors

Stargazers

Watchers

Forkers

rejandsam rstefanetti kcyang lukelu-ca dynamitvectork robertostefanetti so-meca shegen ironminddr

inline-query's Issues

select multiple columns, order by clause

Inline Query should accept multiple columns, and ORDER BY clause

example:
SELECT [No.], Name FROM Customer ORDER BY Name

Need to have a new method "AsRecord" in "Inline Query" codeunit that should accept the following parameters:

Query Text - The above syntax shoule be supported.
RecordRef (Reference type) - WHERE clause (Filters) and ORDER BY clause (Key) should be applied on this Parameter.

INSERT Statement

To insert a record in to a table.

example:
INSERT INTO [Sales Line] ([Document Type], [Document No.], [Line No.], Type, [No.], Quantity) VALUES ('Order', 'ORD-001', 10000, 'Item', 'I-001', 10)

Inline Query Analyzer page not showing the content

Hi,
I tried implementing this app but on Inline Query Analyser page there's nothing. I have no errors in browser console.

Grid doesn't appear

i tried to install your project on mine, but it doesn't show me the grid layout. only json works.

AsJson method in Inline Query Codeunit

"Inline Query" codeunit should have "AsJson" method that should export Query result as Json Array.

example:
JCustomers := InlineQuery.AsJson('SELECT [No.] as code, [Name] as customer_name FROM [Customer]')

Columns should accept Json property names
example:
[No.] as code

In the syntax, "No." is the field name, "code" is the property name in the Json Object.

output:
[{ "code": "C001", customer_name: "ABC"} , { "code": "C002", customer_name: "XYZ"}]

Inline Query Analyzer Control Add-in and Inline Query Analyzer Page

A Control Add-in that accepts Inline Queries (with syntax highlights) and shows results in a Grid

Javascript libraries:
ag-Grid for showing grids, and highlightjs for syntax highlights

Performance and subqueries

I am currently running 2 Query objects to retrieve a combined result. I was thinking of replacing them with one call with the inline query. I have 2 questions:

In the MS Journal blog information under "Limitations / Disadvantages" it is stated that "It compiles the Query Text every time before executing. This is not good for performance.". How much of an impact is does the compilation have? I am trying to compare with 2 Query objects calling the same table with different subqueries.
Can I do subqueries? I am trying to combine the 2 Query objects I am running today into one select query containing 2 subqueries.

Thank you.

msnraju / inline-query Goto Github PK

inline-query's Introduction

Inline Query | SQL like Queries in Business Central

Example

inline-query's People

Contributors

Stargazers

Watchers

Forkers

inline-query's Issues

Recommend Projects

Recommend Topics

Recommend Org