Workaround for X ++ – Select statements with Count and Group by

The possibilities for select statements in X ++ are limited in certain areas compared to T-SQL.
One of these limitations is the combination of count and group by in X ++.
While in T-SQL this query can be mapped with count and distinct, this is not possible in X ++ per select statement.
An example of this is the query for determining all the item numbers used in customer invoice lines with T-SQL:
select count (distinct ItemId) from CustInvoiceTrans
In X ++, the following statement does not help, because the kernel can not make sense of this in T-SQL:
select count (ItemId) from CustInvoiceTrans group by ItemId
However, if you use a query object for help, it is possible to implement this query:
QueryRun::getQueryCount (query, intMax ());
The used query object must contain the field ItemId (item number) from table CustInvoiceTrans (customer invoice items) with group by.
The return value is an integer with the sum of all item numbers used in customer invoice lines.

Teilen per
<< back to news