donderdag 1 oktober 2009

Extracting X++ SQL Statement from AOT Query

When you hover the mouse over a datasource in an AOT query, the select statement is shown in a tooltip.
This select statement represents the query of the datasource above which you hover, and everything below it.
That means that if you hover over the InventDim datasource, the tooltip would show:
SELECT * FROM InventDim WHERE ProdJournalProd.InventDimId = InventDim.inventDimId AND ((NOT (inventBatchId = ' ')))
Often the length of the query-text does not fit the screen.
So, we need a way to extract this sql statement.

I had the following AOT query (inside a report):

Now, how to retrieve the sql statement from the top datasource (in our case, ProdTable) ?

My first attempt was this:
and yielded:
Query Query object 701eef8

The correct way is:
element.query().dataSourceName(literalstr(ProdTable)).toString()and yields:
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 = ' ')))

In a form:

1 opmerking:

  1. My first attempt was info(strfmt("%1",this.query().tosting())); in formdatasource -> executequery()