Help on running direct SQL statement

Finance and Operations/AX Developer Forum
1

HI

I’m a newbie to AX2012, what’s the coorect way to run this SQL query on AX? Can this be run directly like in SQL Server? Is the sintax Correct for AX, what would be the correct way to do this?

SELECT A.PurchReqId, A.PurchReqName, A.Name, A.RequisitionStatus, A.RequisitionPurpose, A.CreatedDateTime,
(SELECT B.UserId FROM B
UNION
SELECT C.Originator FROM C)
FROM PurchReqTable AS A
OUTER JOIN WorkflowWorkItemTable AS B
ON A.RecId = B.RefRecId
OUTER JOIN WorkflowTrackingStatusTable AS C
ON A.RecId = C.ContextRecId

Please your urgent help is greatly appreciated.

Best regards

2

The syntax is documented here: Select Statement Syntax [AX 2012]. Especially if you’re a newbie, you should study the documentation carefully - it’s there to help you. You can also buy books about AX development.

I believe that such a simple query like this will do the job, won’t it?

PurchReqTable a;
WorkflowWorkItemTable b;
WorkflowTrackingStatusTable c;

select ... from a

outer join UserId from b
where b.RefRecId == a.RecId

outer join Originator from c
where c.ContextRecId == a.RecId;