Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

How to get Standard SQL out from Query Object in AOT?

Posted on by 900

Hello,

I'm trting to get SQL out of Query Object made in AOT.

Tried this code BUT it only returns the first SQL clause not all ones combined together.

I think that dataSourceNo(1) <= numer one here restrictes getting all SQL code.

Any ideas, how to do it?

 

Query query;    

QueryBuildDataSource qbd;    

QueryBuildRange qbr;    

QueryRun qr;    

;

query = new Query(queryStr(WorkInstructionsQry));    

qbd = query.addDataSource(TableNum(ProdTable));    

qbr = qbd.addRange(FieldNum(ProdTable, ProdId));    

 qbr.value("PRD_00000062");    

 qr = new QueryRun(query);    

 info(qr.query().dataSourceNo(1).toString());

Br

Mike

 

*This post is locked for comments

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,188 Super User 2024 Season 2 on at
    RE: How to get Standard SQL out from Query Object in AOT?

    Hi "BOM Parent Child items related to released products design",

    Can you create a new question on the forum? This is a very old thread and your question is different from the original one.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to get Standard SQL out from Query Object in AOT?

    I want to write an extracted record to a new table that i created but dont know how to do it, can any one guide me

  • Suggested answer
    Bashir Ahmad Profile Picture
    Bashir Ahmad 5,248 on at
    RE: How to get Standard SQL out from Query Object in AOT?

    The logic i have shared that didn't return the  correct result i have found some work around that will return exact T-SQL

    www.daniellandi.com

  • Suggested answer
    Bashir Ahmad Profile Picture
    Bashir Ahmad 5,248 on at
    RE: How to get Standard SQL out from Query Object in AOT?

    you may try this to get standard SQL from Query object EcoResProductListPage

    Query query;

    query = new Query(queryStr(EcoResProductListPage));

    info(query.dataSourceNo(1).toString());

  • Martin Dráb Profile Picture
    Martin Dráb 230,030 Most Valuable Professional on at
    RE: How to get Standard SQL out from Query Object in AOT?

    I can't say what problem do you have with Trace Parser, but you can also use SQL Server Profiler. I actually prefer it is such situations.

  • Mike Land Profile Picture
    Mike Land 900 on at
    RE: How to get Standard SQL out from Query Object in AOT?

    Hello,

    This tools is behind of ParnerSource program.

    Okey, our Company is in it.

    But I couldn't get it working in AX 2009.

    I checked every details and enabled tracing in AX.

    But everytime this application tried to read the tracefile it crashed.

    Br

    Mike

  • Martin Dráb Profile Picture
    Martin Dráb 230,030 Most Valuable Professional on at
    RE: How to get Standard SQL out from Query Object in AOT?

    If you don't see SQL for child datasource, change FetchMode to 1:1.

    The resulting text is SQL-like language, not T-SQL. For example, is uses EXISTS JOINs, that are translated to subqueries in T-SQL. If you want the real query processed by SQL Server, use tools working on DB level.

  • Mike Land Profile Picture
    Mike Land 900 on at
    RE: How to get Standard SQL out from Query Object in AOT?

    Sorry for typos,

    Br

    Mike

  • Mike Land Profile Picture
    Mike Land 900 on at
    RE: How to get Standard SQL out from Query Object in AOT?

    Hello,

    In this Blog article SQL is Extracted as s´Standard SQL Query

    bmdax.blogspot.fi/.../getting-x-sql-statement-from-aot-query.html

    There is a X++ code showing how the who SQL is get out from starting at highest DS/Table

    element.query().dataSourceName(literalstr(ProdTable)).toString();

    I tried this code but what in my case comes is the SQL only for highest DS/table nothing below it...

    what can be wrong?

    Article also showed SQL that was extracted:

    SELECT * FROM ProdTable GROUP BY ProdTable.ProdId, ProdTable.InventRefType, ProdTable.InventRefId, ProdTable.Name, ProdTable.ItemId, ProdJournalTable.ProdId, InventDim.inventBatchId, InventDim.configId, InventDim.InventSizeId, InventDim.InventColorId WHERE ((ProdId = N'PO0800000403')) JOIN * FROM ProdJournalTable WHERE ProdTable.ProdId = ProdJournalTable.ProdId AND ((Posted = 1)) AND ((JournalType = 1)) JOIN JournalId, SUM(QtyGood) FROM ProdJournalProd WHERE ProdJournalTable.JournalId = ProdJournalProd.JournalId AND ProdJournalTable.JournalType = 1 AND ((NOT (QtyGood = 0))) JOIN * FROM InventDim WHERE ProdJournalProd.InventDimId = InventDim.inventDimId AND ((NOT (inventBatchId = ' ')))

    but when I put this code to SAL Management Studio it says: error near WHERE clause.

    Maybe those JOIN * FROM

    Can anyone explain what's going on?

    Best Regards,

    Mike

  • Suggested answer
    RE: How to get Standard SQL out from Query Object in AOT?

    You could use the trace parcer to get the exact statement all though it is a bit of a big tool for the job.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,188 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,030 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans