Converting sql query to X++ query

Question

I want to get the employee under a specific manager in position hierarchy and the following sql code is used to achive it. Iam new with X++ i want to convert the following sql query to X++ query, but the code that I have writen is not compiling and showing an error message that join is illegal, need your help please.

This is the sql code that I want to convert into x++ code.

select top 1 name from DIRPARTYTABLE
inner join HcmWorker ON HcmWorker.PERSON = DIRPARTYTABLE.RECID
AND HcmWorker.RECID = 
    (select top 1 worker from HCMPOSITIONWORKERASSIGNMENT  
    INNER JOIN HCMPOSITION ON HCMPOSITIONWORKERASSIGNMENT.POSITION =  HCMPOSITION.RECID
    AND HCMPOSITION.RECID = 
        (select TOP 1 ParentPosition from  hcmPositionHierarchy 
            INNER JOIN  hcmPositionHierarchyType on hcmPositionHierarchy.PositionHierarchyType = hcmPositionHierarchyType.RECID AND hcmPositionHierarchyType.HierarchyType = 0
            where hcmPositionHierarchy.Position = 
                (select top 1 POSITION from HCMPOSITIONWORKERASSIGNMENT  
                INNER JOIN HCMPOSITION ON HCMPOSITIONWORKERASSIGNMENT.POSITION =  HCMPOSITION.RECID
                where GETDATE() between HCMPOSITIONWORKERASSIGNMENT.VALIDFROM AND HCMPOSITIONWORKERASSIGNMENT.VALIDTO 
                AND HCMPOSITIONWORKERASSIGNMENT.WORKER = 
                    (select TOP 1 WORKER from hcmEmployment where hcmEmployment.RECID = 5637152077)))
    where GETDATE() between HCMPOSITIONWORKERASSIGNMENT.VALIDFROM AND HCMPOSITIONWORKERASSIGNMENT.VALIDTO);

Following is x++ code that I have writen.

HcmPositionWorkerAssignment     HCMPOSITIONWORKERASSIGNMENT;
    DirPartyTable                   DIRPARTYTABLE;
    HcmPosition                     HCMPOSITION;
    HcmWorker                       HcmWorker;
    HcmPositionHierarchy            hcmPositionHierarchy;
    HcmPositionHierarchyType        hcmPositionHierarchyType;
    HcmEmployment                   hcmEmployment;
    
  select firstonly name from DIRPARTYTABLE
join HcmWorker where HcmWorker.PERSON == DIRPARTYTABLE.RECID
&& HcmWorker.RECID == 
    (select firstOnly worker from HCMPOSITIONWORKERASSIGNMENT  
     JOIN HCMPOSITION where HCMPOSITIONWORKERASSIGNMENT.POSITION ==  HCMPOSITION.RECID
    && HCMPOSITION.RECID == 
        (select firstOnly ParentPosition from  hcmPositionHierarchy 
             JOIN  hcmPositionHierarchyType where hcmPositionHierarchy.PositionHierarchyType = hcmPositionHierarchyType.RECID && hcmPositionHierarchyType.HierarchyType == 0
            where hcmPositionHierarchy.Position == 
                (select firstOnly POSITION from HCMPOSITIONWORKERASSIGNMENT  
                JOIN HCMPOSITION where HCMPOSITIONWORKERASSIGNMENT.POSITION ==  HCMPOSITION.RECID
                where GETDATE() between HCMPOSITIONWORKERASSIGNMENT.VALIDFROM && HCMPOSITIONWORKERASSIGNMENT.VALIDTO 
                && HCMPOSITIONWORKERASSIGNMENT.WORKER == 
                    (select firstOnly WORKER from hcmEmployment where hcmEmployment.RECID == 12345678)))
    where GETDATE() between HCMPOSITIONWORKERASSIGNMENT.VALIDFROM && HCMPOSITIONWORKERASSIGNMENT.VALIDTO);

You could help us answering the question by editing the question and 1) add an explanation in natural language what the query tries to achieve and 2) describe what the issue with the x++ is (does it not compile, does it produce the wrong results, what variations have you tried, ...). — FH-Inway

Sander Sander · Accepted Answer · 2020-08-19T06:39:03

X++ queries do not have the concept of subqueries between round brackets like SQL. You must use a join type or sometimes split the X++ query to achieve the same result. All documentation for the X++ query syntax can be found here.

General remarks for your attempt:

Do not reuse table variables. They are individual table buffers and not just table names like in SQL (somewhat comparable to SQL table aliases).
The SQL construction .RecId = (select top 1 FieldName usually translates to X++ like ... firstonly ... where exists ( ...
exist join in X++ has no field list, you cannot fetch values from this join type.
There are still = comparison operators in your version, they all have to be ==.
Tables HcmPositionWorkerAssignment and HcmEmployment are "valid time state tables". These kind of tables require special attention when querying on them. The between ValidFrom and ValidTo from SQL is automatically applied under the covers (commented code). Querying outside this range requires extra keywords. Examples are available in the documentation.

Applying all this to the SQL query gives the version below. It compiles, but might require some further tweaking to produce the exact same result as SQL.

HcmPositionWorkerAssignment     hcmPositionWorkerAssignment1,
                                hcmPositionWorkerAssignment2;
DirPartyTable                   dirPartyTable;
HcmPosition                     hcmPosition1,
                                hcmPosition2;
HcmWorker                       hcmWorker;
HcmPositionHierarchy            hcmPositionHierarchy;
HcmPositionHierarchyType        hcmPositionHierarchyType;
HcmEmployment                   hcmEmployment;

//utcdatetime refDateTime = DateTimeUtil::getSystemDateTime();

select firstOnly Name from dirPartyTable
    exists join hcmWorker
        where hcmWorker.Person == dirPartyTable.RecId
    exists join hcmPositionWorkerAssignment1
        where hcmPositionWorkerAssignment1.worker == hcmWorker.RecId
    exists join hcmPosition1
        where hcmPosition1.RecId == hcmPositionWorkerAssignment1.Position
    exists join hcmPositionHierarchy
        where hcmPositionHierarchy.ParentPosition == hcmPosition1.RecId
    exists join hcmPositionHierarchyType
        where hcmPositionHierarchyType.RecId == hcmPositionHierarchy.PositionHierarchyType
           && hcmPositionHierarchyType.HierarchyType == 0
    exists join hcmPositionWorkerAssignment2
        where hcmPositionWorkerAssignment2.Position == hcmPositionHierarchy.Position
         //&& hcmPositionWorkerAssignment2.ValidFrom <= refDateTime
         //&& hcmPositionWorkerAssignment2.ValidTo > refDateTime
    exists join hcmPosition2
        where hcmPosition2.RecId == hcmPositionWorkerAssignment2.Position
    exists join hcmEmployment
        where hcmEmployment.Worker == hcmPositionWorkerAssignment2.Worker
           && hcmEmployment.RecId == 12345678;

Converting sql query to X++ query

1 Answers