Finance | Project Operations, Human Resources, ...

Why do Microsoft goes with sql statements instead of using x++ select statements

(6) Share

Report

Posted on by JohnBright

115

Hi experts,

I came across a method in LedgerJournalTable (Table object) under the method numOfVouchers(). This can be achieved through x++ statements as well. But I just want to understand why Microsoft went with sql statement instead of using X++ statements here. This method is used in posting a journal to check whether there are any journal lines there are to be posted. Any inputs is appreciated.

Regards,

John

Categories:

Dynamics 365 Finance

All responses (6)

Answers (0)

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

Like (0)

Report

Why do Microsoft goes with sql statements instead of using x++ select statements

Agree with you. They may simply create a view do a single select on this and get the count. And the distinct keyword can be added in the View definition itself. I think there is some other reason may be.
André Arnaud de Cal... 293,025 Super User 2025 Season 1 on at

Like (2)

Report

Why do Microsoft goes with sql statements instead of using x++ select statements

Hi John,

As Mohamed mentioned, it is because of the distinct keyword. A count of records grouped by voucher gives another result than counting the different voucher numbers.

To achieve the same with X++ queries, you would need to create a select statement grouped by the voucher number and then loop the selection to count the number of vouchers. Using the SQL statement with the distinct statement, there is one call and no additional loops. Performance wise the SQL statement wins.
Deepak Agarwal 8,566 on at

Like (0)

Report

Why do Microsoft goes with sql statements instead of using x++ select statements

This might be related to respective data entity or View maybe. I have seen similar SQL queries written to support view computed fields.
John Bright 72 on at

Like (0)

Report

Why do Microsoft goes with sql statements instead of using x++ select statements

Hi Layan,

Sorry for the delayed response. Here is the code.

public Counter numOfVouchers()
{
SqlSystem sqlSystem = new SqlSystem();

SysDictTable journalTransTable = new SysDictTable(tableNum(LedgerJournalTrans));
FieldName voucherField = journalTransTable.fieldName(fieldNum(LedgerJournalTrans, Voucher));
FieldName journalNumField = journalTransTable.fieldName(fieldNum(LedgerJournalTrans, JournalNum));
FieldName dataAreaField = journalTransTable.fieldName(fieldNum(LedgerJournalTrans, DataAreaId));
FieldName partitionField = journalTransTable.fieldName(fieldNum(LedgerJournalTrans, Partition));

str sql = 'select count(distinct %1) from %2 where %3 = %4 and %5 = %6 and %7 = %8';

sql = strFmt(sql,
voucherField,
journalTransTable.name(),
journalNumField,
sqlSystem.sqlLiteral(this.JournalNum),
dataAreaField,
sqlSystem.sqlLiteral(this.DataAreaId),
partitionField,
getcurrentpartitionrecid());

SqlStatementExecutePermission permission = new SqlStatementExecutePermission(sql);
permission.assert();

//Setup the connection needed to send the command to SQL Server.
Connection userConnection = new Connection();
Statement stmt = userConnection.createStatement();

ResultSet sqlres = stmt.executeQueryWithParameters(sql, SqlParams::create());
CodeAccessPermission::revertAssert();

//Select queries record
sqlres.next();

return sqlres.getInt(1);
}

And yes, I agree the same that Mohamed said can be achieved through group by.

I am just curious why did Microsoft went with an SQL query when the same can be done through x++ select statements.
Layan Jwei 7,722 Super User 2025 Season 1 on at

Like (0)

Report

Why do Microsoft goes with sql statements instead of using x++ select statements

Hi,

it would be great if u can share the code of the method, because not all of us have access to our devBoxes when looking at questions:

But in general based on Mohamed's comment about distinct, then we can still sometimes achieve sth similar to distinct using group by

for example these two sentences below give the same result

select distinct AccountNum from custTable

select AccountNum from custTable group by AccountNum
Suggested answer

Mohamed Amine Mahmoudi 17,301 Super User 2025 Season 1 on at

Like (1)

Report

Why do Microsoft goes with sql statements instead of using x++ select statements

Hi @JohnBright,

because in X++ select statement there's no such thing as a distinct keyword, which is why they use sql statement.

Best regards,

Mohamed Amine MAHMOUDI

Community site session details

Why do Microsoft goes with sql statements instead of using x++ select statements

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

Announcing Our 2025 Season 1 Super Users!

Kudos to the February Top 10 Community Stars!

Leaderboard

Featured topics

Product updates

Community site session details

Why do Microsoft goes with sql statements instead of using x++ select statements

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

Announcing Our 2025 Season 1 Super Users!

Kudos to the February Top 10 Community Stars!

Subscribe to this forum!

Select categories

Leaderboard

Featured topics

Product updates