Complex AX Query

Question

i want to rebuild this SQL Query as AX Query.

I tried it in several ways, but I don't get it.

I am not completely new to AX queries, but I only have experience with some simple queries not with such complex SQL queries.

SELECT * FROM (  SELECT DH.[RECID]                          AS RECID_DIMENSIONHIERARCHY
                      ,DH.[NAME]                            AS NAME__DIMENSIONHIERARCHY
                      ,DH.[DESCRIPTION]                     AS DESC__DIMENSIONHIERARCHY
                      ,DH.[PARTITION]                       AS PARTITION_DIMENSIONHIERARCHY
                      ,DL.[DIMENSIONATTRIBUTE]              AS RECID_DIMENSIONATTRIBUTE
                      ,DA.[NAME]                            AS NAME_DIMENSIONATTRIBUTE        
                      ,DN.[RECID]                           AS RECID_DIMENSIONCONSTRAINTNODE
                      ,DNC.[RECID]                          AS RECID_DIMENSIONCONSTRAINTNODECRITERIA 
                      ,DNC.[RANGETO]                        AS #Owner
                      ,DNCR.[WILDCARDSTRING]                AS #Agreement                                             
             FROM (SELECT * FROM [dbo].[DIMENSIONHIERARCHY]     
                          WHERE [STRUCTURETYPE] = 1 AND [NAME] LIKE 'AG-OW%'  
                  )                                                                          AS DH  
      INNER JOIN [dbo].[DIMENSIONHIERARCHYLEVEL] AS DL
                    ON DH.[RECID]       = DL.[DIMENSIONHIERARCHY]
                AND DH.[PARTITION]      = DL.[PARTITION]     
      INNER JOIN [dbo].[DIMENSIONATTRIBUTE] AS DA
                    ON DL.[DIMENSIONATTRIBUTE]        = DA.[RECID]
                AND DL.[PARTITION]                          = DA.[PARTITION]     
      INNER JOIN [dbo].[DIMENSIONCONSTRAINTNODE] AS DN
                    ON DL.[RECID]                           = DN.[DIMENSIONHIERARCHYLEVEL]
                AND DL.[PARTITION]                          = DN.[PARTITION]   
      INNER JOIN [dbo].[DIMENSIONCONSTRAINTNODECRITERIA]           AS DNC
                    ON DN.[RECID]                           = DNC.[DIMENSIONCONSTRAINTNODE]
                AND DN.[PARTITION]                          = DNC.[PARTITION]   
      INNER JOIN [dbo].[DIMENSIONCONSTRAINTNODECRITERIA] AS DNCR
                    ON DN.[PARENTCONSTRAINTNODE]            = DNCR.[DIMENSIONCONSTRAINTNODE]
                AND DN.[PARTITION]                          = DNCR.[PARTITION]                        
                ) AS Sub

Answer 1

You need to break down your query and implement it in small chunks. Then combine all of it to get the desired result.

There are two ways to create query in X++.

1. Create query using select statement for example:

   Select * from HcmWorker join * from DirPerson where DirPerson.RecId == HcmWorker.Person

   See this link : Select statement syntax

2. Create query with AOT structure. You might want to have a look at the following link:

   Create query in AOT by using X++