Create a query in Visual Studio Code
By entering the tquery shortcut, you can create the basic layout for a Query object when using the AL Extension in Visual Studio Code. The following code sample shows an example of this process.
al-languageCopy
query Id MyQuery
{
QueryType = Normal;
elements
{
dataitem(DataItemName; SourceTableName)
{
column(ColumnName; SourceFieldName)
{
}
filter(FilterName; SourceFieldName)
{
}
}
}
var
myInt: Integer;
trigger OnBeforeOpen()
begin
end;
}
To select the data, you must define the tables from which you want to retrieve the data and the fields that you want to include from those tables. When you are modeling a query, a data item corresponds to a table, and columns correspond to a field in the table.
To define a new dataitem, you need to specify the source table by entering the name of the table. Each dataitem requires a unique name.
For each field that you want to include in the resulting dataset, you must define a column in the query data model. To define a column, insert a new column section under the dataitem and then provide a name and the source fieldname.
QueryType property
The QueryType property helps you define how the query will be used. This property has two possible values, Normal or API, where Normal is the default value. If you want to use a query in a Power BI report or access it by using an API call, you need to set the QueryType property to API. Then, you can publish this query as a web service. The data from a query can only be viewed.
Along with the QueryType property, you also need to set the APIPublisher, APIGroup, APIVersion, EntityName, and EntitySetName properties with an API query.
In APIs, the name of the data items, columns, and filters should use camel casing. Instead of using CustomerNumber or Customer Number, you should use customerNumber.
al-languageCopy
query 20000 "APIV1 - Customer Sales"
{
QueryType = API;
APIPublisher = 'contoso';
APIGroup = 'app1';
APIVersion = 'v1.0';
Caption = 'customerSales', Locked = true;
EntityName = 'customerSale';
EntitySetName = 'customerSales';
elements
{
dataitem(customer; Customer)
{
column(systemId; SystemId)
{
Caption = 'Id', Locked = true;
}
column(customerNumber; "No.")
{
Caption = 'No', Locked = true;
}
column(name; Name)
{
Caption = 'Name', Locked = true;
}
dataitem(custLedgEntry; "Cust. Ledger Entry")
{
DataItemLink = "Customer No." = Customer."No.";
SqlJoinType = LeftOuterJoin;
DataItemTableFilter = "Document Type" = filter(Invoice |
"Credit Memo");
column(totalSalesAmount; "Sales (LCY)")
{
Caption = 'TotalSalesAmount', Locked = true;
Method = Sum;
}
filter(dateFilter; "Posting Date")
{
Caption = 'DateFilter', Locked = true;
}
}
}
}
}
```[oracle certification malaysia](https://lernix.com.my/oracle-certification-malaysia/)
, you might want to change the topic from “Site Feedback” to “Part 1 2022” since this relates to the course notebooks