Hi all,
during the Directions I was asked if it is supported to see or change the T-SQL statement which is build by the query.
Unfortunately this is not included in the Query designer. You can see some sample scripts in the readiness material on partner source to have a better understanding for it.
Of course you can also still use the SQL Profiler to analyze the SQL statement but this doesn’t matter if you are using a query or any other object type, and you are not able to edit it.
These are steps you have to perform to see the T-SQL for Queries
1. Start the session Windows inside Dynamics NAV 2013 and enable „Start Full SQL Tracing“
2. Start All Programs – Microsoft SQL Server – Performance Tools – SQL Server Profiler
3. Click on New Trace / Event Selection
4. Enable
5. Start Tracing
6. Start Query
7. Stop Tracing Look for T-SQL
So for example this is my script for cross selling products based on Sales Invoice Lines
SELECT ISNULL("Item"."No_",@0) AS "No",ISNULL("Item"."Description",@1) AS "Description",ISNULL("CrossLines"."No_",@0) AS "CrossLine_No",ISNULL("CrossLines"."Description",@1) AS "CrossLine_Description",ISNULL(COUNT("CrossLines"."Document No_"),@4) AS "Count_" FROM "Demo Database NAV (7-0)".dbo."CRONUS International Ltd_$Item" AS "Item" WITH(READUNCOMMITTED) LEFT OUTER JOIN "Demo Database NAV (7-0)".dbo."CRONUS International Ltd_$Sales Invoice Line" AS "Sales_Invoice_Line" WITH(READUNCOMMITTED) ON ("Sales_Invoice_Line"."No_"="Item"."No_") LEFT OUTER JOIN "Demo Database NAV (7-0)".dbo."CRONUS International Ltd_$Sales Invoice Line" AS "CrossLines" WITH(READUNCOMMITTED) ON ("CrossLines"."Document No_"="Sales_Invoice_Line"."Document No_") WHERE (ISNULL("Item"."No_",@0)=@2) AND ("Sales_Invoice_Line"."Type"=@3) AND (ISNULL("CrossLines"."No_",@0)<>@5) GROUP BY ISNULL("Item"."No_",@0),ISNULL("Item"."Description",@1),ISNULL("CrossLines"."No_",@0),ISNULL("CrossLines"."Description",@1) ORDER BY "No" ASC,"Description" ASC,"CrossLine_No" ASC,"CrossLine_Description" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50, FORCE ORDER, LOOP JOIN)