Question Status

Suggested Answer
Mike Land asked a question on 19 Mar 2013 2:29 AM

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

 

Reply
Suggested Answer
Søren Rasmussen responded on 19 Mar 2013 5:43 AM

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

Reply
Martin Dráb responded on 19 Mar 2013 5:41 AM

You created a query with two root datasources, but you call toString() only on the first one. There is no "query combined together", you simply have two queries. What you probably want is to get an existing datasource from the query and add ProdTable as its child, not as a root-level datasource. Another thing that you might be trying to do is UNION query.

Martin "Goshoom" Dráb | Freelancer | Goshoom.NET Dev Blog

Reply
Suggested Answer
Søren Rasmussen responded on 19 Mar 2013 5:43 AM

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

Reply
Mike Land responded on 20 Mar 2013 1:43 AM

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

Reply
Mike Land responded on 20 Mar 2013 1:58 AM

Sorry for typos,

Br

Mike

Reply
Martin Dráb responded on 20 Mar 2013 2:08 AM

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.

Martin "Goshoom" Dráb | Freelancer | Goshoom.NET Dev Blog

Reply
Mike Land responded on 20 Mar 2013 9:52 AM

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

Reply
Martin Dráb responded on 20 Mar 2013 10:09 AM

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.

Martin "Goshoom" Dráb | Freelancer | Goshoom.NET Dev Blog

Reply