Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Unable to Convert SQL ...
Microsoft Dynamics AX (Archived)

Unable to Convert SQL query in x++ for RDP Class?

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi Everyone,

I have written a query in SQL (report builder ) and it is working fine but now i want to use this query in x++ RDP class for SSRS.

below is the sql query :

x-----------------------x-------------------------------------------------------x

from hcmworker

inner join DirpartyTable on hcmworker.PERSON = DirpartyTable.RECID
inner join dirPartyLocation on dirPartyTable.RecId = dirPartyLocation.Party
inner join HcmPositionWorkerAssignment on HcmPositionWorkerAssignment.Worker = hcmworker.RECID

inner join HcmPositionDetail on HcmPositionWorkerAssignment.Position=HcmPositionDetail.Position


inner join dimattributeomdepartment on dimattributeomdepartment .RECID=HcmPositionDetail.department


inner join logisticsLocation on dirPartyLocation.Location = logisticsLocation.RecId


inner join hcmemployment on hcmworker.recid=hcmemployment.worker
left outer join  assettable on hcmworker.personnelnumber= assettable.personnelnumber

--and LOGISTICSELECTRONICADDRESS.LOCATOR LIKE '%computers%'






where HcmPositionDetail.VALIDFROM <= getdate()
and HcmPositionDetail.VALIDTO >= getdate()
and hcmemployment.VALIDTO >= getdate()
--and hcmemployment.VALIDFROM >=getdate()

and ASSETTABLE.ASSETID IS NULL
OR assettable.assetgroup='computers'
and HcmPositionDetail.VALIDTO >= getdate()

ORDER BY hcmworker.PERSONNELNUMBER DESC

x-----------------------x-------------------------------------------------------x

Please help me in converting this query into X++.

i got stuck on how to handle getdate() and NULL query.

thank you

*This post is locked for comments

  • Zeeshan Adeel Profile Picture
    Zeeshan Adeel 335 on at
    RE: Unable to Convert SQL query in x++ for RDP Class?

    will you share the code with me?

    i also want to get active workers list with their details WITHOUT using Validfrom and validto.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Unable to Convert SQL query in x++ for RDP Class?

    Well elaborated martin.

    Actually i have modified the query by removing some unnecessary joins  and now i am getting active workers list with their details WITHOUT using Validfrom and validto.

    Thanks both for being there.

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 231,305 Most Valuable Professional on at
    RE: Unable to Convert SQL query in x++ for RDP Class?

    I see several problems with the solution (e.g. that all the nested query are bad for performance), but the most severe one is that the outer query is wrong. Just ignoring the fact that the table is date-effective doesn't change the reality.

    Look at what query is sent to database (I've simplified it for readability):

    SELECT * FROM HCMWORKER T1
CROSS JOIN HCMEMPLOYMENT T2
WHERE T2.WORKER=12345
AND T2.VALIDTO>='2017-11-02T07:46:45'
AND T2.VALIDFROM<='2017-11-02T07:46:45'
AND T2.VALIDTO>='2017-11-02T07:46:45'

    Do you see how there is a standard filter for the current period plus your extra range? It doesn't make sense.

    If your intention was to see all records until the current time, you have a bug, because there is the filter on ValidFrom.

    If you're happy with the current period, you can throw away your filter on ValidTo, because it merely duplicates what's already there by default.

    If you want to change the period applied to date-effective tables, you must use validTimeState keyword as I mentioned in my first reply.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Unable to Convert SQL query in x++ for RDP Class?

    Sukruk , Thank you so much for  your time. Just work for me now :)

  • Martin Dráb Profile Picture
    Martin Dráb 231,305 Most Valuable Professional on at
    RE: Unable to Convert SQL query in x++ for RDP Class?

    Why do you think it's wrong? What problems have you identified with it?

    If the problem is that it doesn't filter by ValidTo, I already answered it above.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Unable to Convert SQL query in x++ for RDP Class?

    ok thanks a lot - last thing, can you please identify whats wrong with the query , it will save my time, (already wasted)

    utcdatetime  validTimeStateFrom = DateTimeUtil::minValue();

    utcdatetime validTimeStateTo= DateTimeUtil::maxValue();

          while select validTimeState(validTimeStateFrom,validTimeStateTo) * from hcmWorker

            where hcmEmployment.Worker == hcmWorker.RecId

              // && hcmEmployment.ValidTo >= DateTimeUtil::utcNow()

               join hcmemployment where  hcmworker.recid==hcmemployment.worker

              join DirpartyTable where hcmWorker.PERSON == dirpartyTable.RECID  

    join dirPartyLocation  where dirPartyTable.RecId == dirPartyLocation.Party                

              join HcmPositionWorkerAssignment where hcmPositionWorkerAssignment.Worker== hcmWorker.RECID

              join hcmemployment where  hcmWorker.recid==hcmEmployment.worker

  • Martin Dráb Profile Picture
    Martin Dráb 231,305 Most Valuable Professional on at
    RE: Unable to Convert SQL query in x++ for RDP Class?

    If you still have a problem with the query, forget the report and focus on the query. The report can't work if the query is broken, so playing with the report is just a waste of time. You should look at the query you've generated and compare it with the intended query; then you can see what you have done wrong.

    If the query works and you have another problem with a report, please create a new thread the new question.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Unable to Convert SQL query in x++ for RDP Class?

    My problem is that i want to generate a list of Active workers with having Personnel Number, Name, Position and Department fields on the report.i achieved this in SQL but unable to do in X++.

    FYI not working means not generating data on the report.

    now please resolve it. :P : )

  • Martin Dráb Profile Picture
    Martin Dráb 231,305 Most Valuable Professional on at
    RE: Unable to Convert SQL query in x++ for RDP Class?

    Could you describe the problem, please? I have no solution for "not working".

    Also, I thought you wanted to limit ValidTo, so it's not clear to me why you're using DateTimeUtil::maxValue().

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,489 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,305 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans