PRODROUTE - WTRKCTRID -IN SQL QUERY

Hi ,

We are using AX 2012 r2 ,we would like to get wrkctrid from SQL Query to provide the data to 3’rd party software .

Below Query not getting complete records as we have ROUTEOPRREFRECID is blank in prodroute table .

Kindly advise right sql query to get to the wrkctrid.

FROM PRODROUTE AS T JOIN

PRODTABLE AS T7 ON T.PRODID=T7.PRODID JOIN

ROUTEOPR AS T1 ON T.ROUTEOPRREFRECID=T1.RECID join

INVENTTABLE as NT1 oN t7.ITEMID=Nt1.ITEMID AND NT1.DATAAREAID=t1.DATAAREAID JOIN

WRKCTRROUTEOPRACTIVITY AS T2 ON T1.RECID=T2.ROUTEOPR JOIN

WRKCTRACTIVITYREQUIREMENTSET AS T3 ON T3.ACTIVITY=T2.ACTIVITY JOIN

WRKCTRACTIVITYREQUIREMENT T4 ON T4.ACTIVITYREQUIREMENTSET=T3.RECID JOIN

WRKCTRACTIVITYRESOURCEREQUIREMENT AS T5 ON T5.ACTIVITYREQUIREMENT=T4.RECID JOIN

WRKCTRTABLE AS T6 ON T6.WRKCTRID=T5.WRKCTRID

Thanks in advance .

Raj

First of all, let me move your question to the developer forum and format your code to make it slightly easier to read. Next time, please use Insert > Insert Code to paste source code.

FROM PRODROUTE AS T JOIN
PRODTABLE AS T7 ON T.PRODID=T7.PRODID JOIN
ROUTEOPR AS T1 ON T.ROUTEOPRREFRECID=T1.RECID join
INVENTTABLE  as NT1 oN t7.ITEMID=Nt1.ITEMID AND NT1.DATAAREAID=t1.DATAAREAID JOIN
WRKCTRROUTEOPRACTIVITY AS T2 ON T1.RECID=T2.ROUTEOPR JOIN
WRKCTRACTIVITYREQUIREMENTSET AS T3 ON T3.ACTIVITY=T2.ACTIVITY JOIN
WRKCTRACTIVITYREQUIREMENT T4 ON T4.ACTIVITYREQUIREMENTSET=T3.RECID JOIN
WRKCTRACTIVITYRESOURCEREQUIREMENT AS T5 ON T5.ACTIVITYREQUIREMENT=T4.RECID JOIN
WRKCTRTABLE AS T6 ON T6.WRKCTRID=T5.WRKCTRID

One obvious problem with your code is that you don’t handle data areas and partitions. For example, WrkCtrlTable is company-specific and you forget to filter it by partition and DataAreaId. This is just one of many reasons why you shouldn’t access AX DB directly. AX would handle it automatically for you. The other reasons include bypassing security, the necessity to allow connections to database, the fact that you can’t use table relations and business logic already written in X++, and so on and so on.

Also, it’s not clear to me what you mean by the description of your problem (“Below Query not getting complete records as we have ROUTEOPRREFRECID is blank in prodroute table”). Do you mean that the query doesn’t return records where RouteOprRefRecId is empty? if so, the reason it that you’re using an inner join. If you use LEFT OUTER JOIN, the query will return ProdRoute even if the related tables don’t have any corresponding records.

Dear Martin,

Thanks for your valuable input always.

The problem in my Query Prodroute table Routeoprrefrecid values are empty for few Production orders. Please refer the attached below screenshot for your reference.

My Join is broken at routeropr, is there any way to pull the wrkctrid without using routeopr Table in Sql .

Below My Query works fine where routeoprrefrecid is not blank in prodroute table.

Thanks

Raj