Finance | Project Operations, Human Resources, ...

how can i convert sql query into x++ query

(1) Share

Report

Posted on by Dineshkarlekar

1,479

i am tryin to convert sql query in x++ query can anyone tel me how can i do it i am getting proper result in sql but not in my x++ query here is my sql query , i place of /sa03 / i want to pass current company and in place of /10001/ i want to pass all the workers in current company i want to convert it into while x++ query can any one help me out on this

All responses (6)

Answers (0)

Suggested answer

Bharani Preetham Pe... 3,624 Moderator on at

Like (0)

Report

how can i convert sql query into x++ query

Instead simple create a view and then check how it is behaving in ssms. Then right a select or while select on it.
Community member 2 on at

Like (0)

Report

how can i convert sql query into x++ query

To convert your SQL query into X++ query, you can use X++ syntax for database operations. Replace "/sa03" with the current company and "/10001" with all workers in the current company using X++ variables and conditions. Ensure you follow X++ conventions for database queries and use appropriate functions like select or update to achieve your desired result.
Suggested answer

Umesh Pandit 9,303 User Group Leader on at

Like (0)

Report

how can i convert sql query into x++ query

Translating a SQL query into an X++ query largely depends on the context and structure of your code, as well as the data model you're working with. X++ is the programming language used in Microsoft Dynamics AX (now part of Dynamics 365 Finance and Operations) for customizing and extending the ERP system.

Here's a general outline of how you might approach converting a simple SQL query to an X++ query:

Assuming you have a SQL query like this:

SELECT Column1, Column2
FROM TableName
WHERE SomeCondition;

You might convert it to an X++ query like this:

TableName tableName;
Query query = new Query();
QueryBuildDataSource queryBuildDataSource;

queryBuildDataSource = query.addDataSource(tableNum(tableName));
queryBuildDataSource.addRange(fieldNum(tableName, ColumnName)).value('SomeValue');

while (queryRun.next())
{
// Access data using queryRun.get()
}

In this example, TableName would be the Dynamics AX table name, ColumnName would be the field/column you're filtering on, and 'SomeValue' would be the value you're filtering for.

Keep in mind that X++ is quite different from SQL, and its query structure revolves around manipulating data using data sources, ranges, and query objects.

However, for more complex queries involving joins, aggregations, or more advanced filtering, the X++ code would become more intricate. It's also important to consider that X++ queries might not always map one-to-one with SQL queries, due to the differences in the underlying data structures and query engines.
GirishS 27,825 Moderator on at

Like (0)

Report

how can i convert sql query into x++ query

Hi Dinesh,

I would convert this into dynamic query instead of while select. So as per your select statement need to join CustTable, CustTrans, DTFinancialDimensionView, HcmWorker, HcmEmployment. On HcmEmployment there will be a field named LegalEntity - You need to add Current CompanyInfo table recid as a range. This will filter only the current company workers.

Thanks,

Girish S.
Community member 2 on at

Like (0)

Report

how can i convert sql query into x++ query

To convert your SQL query into an X++ query, replace '/sa03/'with the current company and '/10001/'with all workers within that company. Adapt the SQL logic to X++ syntax for your desired result. If you're facing issues, consider sharing the X++ query you've attempted for more specific guidance.
Dineshkarlekar 1,479 on at

Like (0)

Report
how can i convert sql query into x++ query

select Worker from custTable join custTrans on custTable.AccountNum = custTrans.AccountNum and custTrans.CLOSED = '' join DTFinancialDimensionView on DTFinancialDimensionView.RecordId = custTrans.DefaultDimension where custTrans.DATAAREAID = 'sa03' group by DTFinancialDimensionView.Worker