How to see the SQL expression of an X++ select statement Pre Ax2012?

Question

I am using Axapta 4.0 and want to see what SQL Statement is generated out of my x++ code.

How can I see the generated SQL Statement ?

Some background so you might understand why I want to see the code. My X++ Sourcecode compiles but during execution I get the message that it can't join the tables because the where clause is wrong (not matching). I looked it up in the AOT and it matches.

I read this how-to-see-the-sql-expression-of-an-x-select-statement but it seems that it only applys to AX2012 and not Axapta4.0

I have the big statement which isn't working here

select *
    FROM ltabInventTable
    join * FROM ltabModelList
        where  ltabModelList.ItemId == ltabInventTable.ItemId
        && ltabModelList.IsAssortment == 0
    join * FROM ltabInventTableModule
        where ltabInventTableModule.ITEMID == ltabInventTable.ITEMID
        && ltabInventTableModule.ModuleType == 1
    join * FROM ltabCharValue
        where ltabCharValue.CHARACTERVALUE == ltabModelList.MODELLCONFIGID
    join * FROM ltabBarcodes
        where ltabBarcodes.ITEMID == ltabInventTable.ITEMID
    join * from ltabInventDim
        where ltabInventDim.INVENTDIMID == ltabBarcodes.InventDimId
    join * FROM ltabConfigTable
        where ltabConfigTable.ITEMID == ltabBarcodes.ITEMID
        && ltabConfigTable.CONFIGID == ltabInventDim.CONFIGID
        && ltabConfigTable.MODELCONFIGID == ltabModelList.MODELLCONFIGID
    outer join ltabPriceDiscItemResult //IN HERE WE HAVE THE PROBLEM
       where ltabPriceDiscItemResult.ITEMID == ltabModelList.ITEMID
        && ltabPriceDiscItemResult.MODELCONFIGID == ltabModelList.MODELLCONFIGID
        && ltabPriceDiscItemResult.SIZEID == ltabModelList.SizeID;

And I have a smaller version which works.

SELECT * FROM ltabModellist //AWESOME THIS WORKS
    outer join ltabPriceDiscItemResult
       where ltabPriceDiscItemResult.ITEMID == ltabModelList.ITEMID
        && ltabPriceDiscItemResult.MODELCONFIGID == ltabModelList.MODELLCONFIGID
        && ltabPriceDiscItemResult.SIZEID == ltabModelList.SizeID;

The smaller version is joining exactly like the bigger one but the bigger one is throwing an error.

Edit:

The error message was requested. The problem is that the error is only in german. i will provide both the german error message and the google translation of it.

Original:

Ein Datensatz in Resultat Preisabschrift (PriceDiscItemResult) kann nicht ausgewählt werden. Der Join enthält in der WHERE-Klausel keine Verknüpfung zwischen den Join-Tabellen.

Google translation:

A record in result pricemarkdown (PriceDiscItemResult) can not be selected. The join in the WHERE clause contains no link between the join tables.

I hate to state the obvious, but someone has to do it... Your first select is missing a semi-colon at the end? — ian_scho, Jul 22 '16 at 06:17
Oh yeah, but that got probably lost during copy and paste. I tried the same as while select and that wasn't working either. But thanks, if it was only a semicolon I would feel a little bit embarassed right now :). I will fix it above — Bongo, Jul 22 '16 at 07:34
@ian_scho it wouldn't had surprised me if it was a semicolon. I am fairly new to axapta — Bongo, Jul 22 '16 at 07:38
Try moving the outer join before the other joins (see [Error message when using outer join for a form data source](http://dynamicsuser.net/ax/f/11/t/70644)) — FH-Inway, Jul 22 '16 at 11:00
I solved the problem now with a UserConnection. Thanks for all the help so far. The question stays the same though: How can I see the generated SQL Statement ? — Bongo, Jul 22 '16 at 12:52

Pradeep Muttikulangara Vasu · Answer 1 · 2016-07-26T18:22:08.300

2

Use Sql profiler to capture the sql statements.

Below blog explains it in details.

Brandon’s Dynamics AX SQL Server Profiler Recipe to see the underlying Tables and SQL

To know more about SQL Profiler refer msdn article. SQL Server Profiler

edited Jul 26 '16 at 18:22

answered Jul 24 '16 at 06:17

Pradeep Muttikulangara Vasu

426
4
11

1

I would say this answer is correct and I would give it an upvote but you should provide more Context, so in case that the Video or the blog is deleted, other users will still know what to do. – Bongo Jul 25 '16 at 08:28

score 0 · Answer 2 · answered Jul 25 '16 at 14:54

There are some tracing settings in the user options in the AX client on the SQL tab that allow you to log sql statements in various outputs (file, table, infolog). See Tracing with the Tools Menu for more information.

Also the configuration utilities for the server and client have some tracing options (see Set tracing options for more information).

How to see the SQL expression of an X++ select statement Pre Ax2012?

2 Answers2