复杂AX查询

Question

我希望将此SQL查询重新构建为AX查询。

我试过几种方法，但我不明白。

我对AX查询并不完全陌生，但我只对一些简单的查询有经验，而不是使用这样复杂的SQL查询。

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

您需要分解查询并将其实现为小块。然后将所有这些组合起来，以获得所需的结果。

在X++中创建查询有两种方法。

1. 使用select语句创建查询，例如： Select * from HcmWorker join * from DirPerson where DirPerson.RecId == HcmWorker.Person 请参阅此链接：选择语句语法
2. 使用AOT结构创建查询。您可能需要查看以下链接： 使用X++在AOT中创建查询