while select group by month statement

(0) Share

Report

Posted on by dynamicscon

Hello there,

I can get the results as I want in my query that I wrote in sql, but when I try to get the same result with x while select, I get the error "join' expected". I will populate my temp table based on my query result.

how can i overcome this problem?

sql query:

select COMPANYID, sum(QUERYCOUNT), sum(QUERYROWCOUNT) from ARCCAMPAIGNQUERYLOG group by COMPANYID, month(QUERYDATE)

x query :

while select CompanyId, sum(QueryCount), sum(QueryRowCount)
from arcCampaignQueryLog
group by CompanyId, mthOfYr(QueryDate)
{

}

All responses (3)

Answers (0)

dynamicscon 80 on at

Like (1)

Report

RE: while select group by month statement

Yes unfortunately I solved this problem by opening a field in the format I want.
Martin Dráb 232,896 Most Valuable Professional on at

Like (0)

Report

RE: while select group by month statement

As Girish said, you can't use X++ functions in database queries.

A solution may be using a computed column. Unfortunately, you didn't specify your version of AX, therefore we can't know whether your version supports computed columns.
GirishS 27,825 Moderator on at

Like (0)

Report

RE: while select group by month statement

Hi Muhammed Hakan Celi,

You cannot use function in group by like mthofYr(QueryDate). You can only use direct table level fields in group by.

Thanks,

Girish S.