SQL to AX query

(0) Share

Report

Posted on by Community Member

Hi all,

i want to create the following SQL query in AX as a query!

Have anybody a idea how to do this? I tried a lot but i didn't get it.

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

--                        ,DA.[REPORTCOLUMNNAME]          

                          ,DN.[RECID]                           AS RECID_DIMENSIONCONSTRAINTNODE

--                        ,DN.[DIMENSIONHIERARCHYLEVEL] AS DL0

--                        ,DNC.[WILDCARDSTRING]                 AS W1

--                        ,DNC.[ORDINAL]                        AS O2

                          ,DNC.[RECID]                                 AS RECID_DIMENSIONCONSTRAINTNODECRITERIA 

                          ,DNC.[RANGETO]                        AS #Owner

--                        ,DNC.[RANGEFROM]                      AS RANGEFROM

                          ,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

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (0)

Martin Dráb 237,987 Most Valuable Professional on at

Like (0)

Report

Which part are tou struggling with? If you know how to use joins, for example, you can likely remove most of code from your example and then we can focus on what you actually need.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I am struggling in that Point, that i don't know in which order i have to drag an drop tables into the query and further i don't know how to create the first part of the SQL Statement in an AX query. The part i mean is "Select * from ( Select ....)"

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

Hi JamesnxD,

If you talking about drag and drop I can assume that you asking about AOT query. Order of tables in query would be the same as you have in SQL statement. To do "select *" you just need to set dynamicFIelds property to "Yes".

On msdn you can find information and examples regarding AOT query creation msdn.microsoft.com/.../bb314753.aspx

msdn.microsoft.com/.../aa880078.aspx

Was this reply helpful? Yes No
Martin Dráb 237,987 Most Valuable Professional on at

Like (0)

Report
I tried to tell you that you'll get overwhelmed if you try to deal with too many problems at once. Why don't you start with a simpler query and complicate things only after you learn basics?

It seems that your current question is how to implement this:

SELECT * FROM ( SELECT DH.[RECID] FROM ( SELECT * FROM [dbo].[DIMENSIONHIERARCHY]) AS DH)

I would first simplify the SQL code before trying to convert it to AX query:

SELECT DH.[RECID] FROM [dbo].[DIMENSIONHIERARCHY] AS DH

Now your task is much easier. Add the DimensionHierarchy to your query, set DynamicFields = No and add RecId to the field list. Then check if it return the right SQL code. If it does, well done - you've managed to create a simple query and you can take another step toward the whole query.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you for your answer, but this is exactly the point!

I can create in AX a query like:

SELECT DH.[RECID] FROM [dbo].[DIMENSIONHIERARCHY] AS DH

but what are the step after this?

In the SQL statement it selects a resultset from an select and not a table.

I think my main problem is, that i don't know how to create a query where the datasource is another query.

Sorry I have not expressed myself clearly.

Was this reply helpful? Yes No
Suggested answer

Faisal Fareed 10,796 User Group Leader on at

Like (0)

Report

Hi James,

If you are looking for a select statement in AX which would be similar to what you have provided for SQL then following is the way to convert your statment;

Select DimensionHierarchy; // this will return the first record of the table with all the available fields in table

Select Description, Name from DimensionHierarchy; // this will return the first record of the table with all the selected fields in query

Now, if you want to create query in AOT then do followin;

Right click to AOT\Query node and create new query

Under Data Sources node, drag and drop DimensionHierarchy table

Expand the Data Source node until Fields and take properties

Select YES to Dynamic

You can easily play around with this approach to add more data sources into your query with proper joins as required.

Was this reply helpful? Yes No
Martin Dráb 237,987 Most Valuable Professional on at

Like (0)

Report

AX doesn't support sub-queries, although you can create a view based on a query and use it in another query. But I wouldn't do something such complex if the sub-query isn't needed at all. I believe that it's the case - your SQL code seems to be much more complicated that it needs to be, therefore you should refactor it to some more sensible before anything else.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for all your answers! I will try some different things and post my result.

Maybe i write the statement in X++, i will see.

Was this reply helpful? Yes No
karthickarden 135 on at

Like (0)

Report

Hi All,

Anyone can tell how to find system query and custom query in the AOT Node.

Is there any queries available to differentiate that?

Thanks

Karthick.M

Was this reply helpful? Yes No
Faisal Fareed 10,796 User Group Leader on at

Like (0)

Report

Hi Karthic,

I would say create new question for your requirement with more details. Put some screen shots to get quick answer.

Was this reply helpful? Yes No