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

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Mohamed Amine Mahmoudi 26,449 Super User 2025 Season 2 on at

Like (1)

Report

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

Was this reply helpful? Yes No
Layan Jwei 8,124 Super User 2025 Season 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
John Bright 141 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Deepak Agarwal 8,602 on at

Like (0)

Report

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

Was this reply helpful? Yes No
André Arnaud de Cal... 301,231 Super User 2025 Season 2 on at

Like (2)

Report

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.

Was this reply helpful? Yes No
Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No