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

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (16)

Answers (0)

Sort by

Martin Dráb 239,392 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!

Regarding date-effective data, use validTimeState keyword in the select statement. Look at the documentation - Select Statement Syntax - including comments below it.

Null values aren't supported in AX. Maybe you want to use a nonexists join instead?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

FYI I have started my query with

while select * from hcmWorker

and then inner joins

can you please elaborate how to use validTimeState keyword to use with "and HcmPositionDetail.VALIDTO >= getdate()"

thank you

Was this reply helpful? Yes No
Suggested answer

Mahmoud Hakim 17,887 on at

Like (1)

Report
Copy link

Link copied!

if you are expert in SQL, you can create a data source in visual studio and create your query with the same syntax as the below

or as sukrat said "divide your query in RDP"

create the report and select data source type "your data source"

after that click query and write as below

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 239,392 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!

Maqbool, use validTimeState keyword at the top level, i.e. while select validTimeState(...) * from hcmWorker. It should apply to the whole query.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

@sukrut Parab Thank you so much for the detail response. Let me try and get back to you

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Mahmoud Hakim haha why this idea dont come in the mind. Thank you,

Actually in that case i have to give rights to the end user (sql read only) to access reports.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Mr. Martin & Sukrut i have modify the query now its look like :

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

but still not working ,

thank you

Was this reply helpful? Yes No
Martin Dráb 239,392 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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().

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No