Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Unanswered

while select group by month statement

Posted on by 80

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)
{

}

  • dynamicscon Profile Picture
    dynamicscon 80 on at
    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 Profile Picture
    Martin Dráb 225,549 Super User on at
    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 Profile Picture
    GirishS 27,826 Super User on at
    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.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,914 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,549 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans