Dynamics Community Forum Thread Details

Hi Guys,

As background. We are developing a product that will do /direct query/ to the underlying BC database on prem (or copies of it for cloud).

The product will use hand written SQL (stored in the cloud) to query BC.

This means the SQL can be much more complex than other query methods and therefore we will be able to answer much more complex questions just using Direct Query.

We will send the data to Excel / PowerBI for the /finished report/.

So anyone who has a report / dashboard they have /always wanted/ but can't get it done because it is too complex?

We would be happy to hear from you and try and produce your report for you because that will be a win win situation.

We know Navision well but we are new to BC so we have some learning to do.

I ran a trace while running a GL report in BC itself and the query below is what the trace produced.

So it's doing some /different/ things from NAV.

If any developers are willing to answer my questions that would be much appreciated.

Thank you in advance for any assistance you might be willing to provide.

Q1.

When we try and run the generated SQL in SSMS we will get message like:

Msg 8179, Level 16, State 6, Procedure sp_prepexec, Line 1 [Batch Start Line 0]
Could not find prepared statement with handle 96.

Or whatever the @p1 is set to.

I am guessing that inside BC there is some variable allocated and the 96 is somehow known.

Is there any way to run these sorts of queries captured in the traces /as is/ in SSMS?

Meaning, what does this 96 point to and how could we allocate something like this in the SSMS session so that the query will run?

Q2.

I am curious why /outer apply/ has been used so liberally as opposed to just a left join.

Is there some reason why outer apply is used that I am not aware of?

We simply never use it in BI reporting because left joins are enough.

declare @p1 int
set @p1=96
exec sp_prepexec @p1 output
,N'@0 nvarchar(20)'
,N'SELECT TOP (50) /G_L Account/./timestamp/ AS /timestamp/
,/G_L Account/./No_/ AS /No_/
,/G_L Account/./Name/ AS /Name/
,/G_L Account/./Search Name/ AS /Search Name/
,/G_L Account/./Account Type/ AS /Account Type/
,/G_L Account/./Global Dimension 1 Code/ AS /Global Dimension 1 Code/
,/G_L Account/./Global Dimension 2 Code/ AS /Global Dimension 2 Code/
,/G_L Account/./Account Category/ AS /Account Category/
,/G_L Account/./Income_Balance/ AS /Income_Balance/
,/G_L Account/./Debit_Credit/ AS /Debit_Credit/
,/G_L Account/./No_ 2/ AS /No_ 2/
,/G_L Account/./Blocked/ AS /Blocked/
,/G_L Account/./Direct Posting/ AS /Direct Posting/
,/G_L Account/./Reconciliation Account/ AS /Reconciliation Account/
,/G_L Account/./New Page/ AS /New Page/
,/G_L Account/./No_ of Blank Lines/ AS /No_ of Blank Lines/
,/G_L Account/./Indentation/ AS /Indentation/
,/G_L Account/./Last Modified Date Time/ AS /Last Modified Date Time/
,/G_L Account/./Last Date Modified/ AS /Last Date Modified/
,/G_L Account/./Totaling/ AS /Totaling/
,/G_L Account/./Consol_ Translation Method/ AS /Consol_ Translation Method/
,/G_L Account/./Consol_ Debit Acc_/ AS /Consol_ Debit Acc_/
,/G_L Account/./Consol_ Credit Acc_/ AS /Consol_ Credit Acc_/
,/G_L Account/./Gen_ Posting Type/ AS /Gen_ Posting Type/
,/G_L Account/./Gen_ Bus_ Posting Group/ AS /Gen_ Bus_ Posting Group/
,/G_L Account/./Gen_ Prod_ Posting Group/ AS /Gen_ Prod_ Posting Group/
,DATALENGTH(/G_L Account/./Picture/) AS /Picture/
,/G_L Account/./Automatic Ext_ Texts/ AS /Automatic Ext_ Texts/
,/G_L Account/./Tax Area Code/ AS /Tax Area Code/
,/G_L Account/./Tax Liable/ AS /Tax Liable/
,/G_L Account/./Tax Group Code/ AS /Tax Group Code/
,/G_L Account/./VAT Bus_ Posting Group/ AS /VAT Bus_ Posting Group/
,/G_L Account/./VAT Prod_ Posting Group/ AS /VAT Prod_ Posting Group/
,/G_L Account/./Exchange Rate Adjustment/ AS /Exchange Rate Adjustment/
,/G_L Account/./Default IC Partner G_L Acc_ No/ AS /Default IC Partner G_L Acc_ No/
,/G_L Account/./Omit Default Descr_ in Jnl_/ AS /Omit Default Descr_ in Jnl_/
,/G_L Account/./Account Subcategory Entry No_/ AS /Account Subcategory Entry No_/
,/G_L Account/./Cost Type No_/ AS /Cost Type No_/
,/G_L Account/./Default Deferral Template Code/ AS /Default Deferral Template Code/
,/G_L Account/./Id/ AS /Id/
,/G_L Account/./API Account Type/ AS /API Account Type/
,/G_L Account/./$systemId/ AS /$systemId/
,/G_L Account/./$systemCreatedAt/ AS /SystemCreatedAt/
,/G_L Account/./$systemCreatedBy/ AS /SystemCreatedBy/
,/G_L Account/./$systemModifiedAt/ AS /SystemModifiedAt/
,/G_L Account/./$systemModifiedBy/ AS /SystemModifiedBy/
,ISNULL(/SUB$Net Change/./Net Change$G_L Entry$SUM$Amount/,0.0) AS /Net Change/
,ISNULL(/SUB$Balance/./Balance$G_L Entry$SUM$Amount/,0.0) AS /Balance/
,ISNULL(/SUB$Account Subcategory Descript_/./Account Subcategory Descript_$G_L Account Category$LOOKUP$Description/
,N'''') AS /Account Subcategory Descript_/
,ISNULL(/SUB$Debit Amount/./Debit Amount$G_L Entry$SUM$Debit Amount/,0.0) AS /Debit Amount/
,ISNULL(/SUB$Credit Amount/./Credit Amount$G_L Entry$SUM$Credit Amount/,0.0) AS /Credit Amount/
,ISNULL(/SUB$Balance at Date/./Balance at Date$G_L Entry$SUM$Amount/,0.0) AS /Balance at Date/
,ISNULL(/SUB$Additional-Currency Net Change/./Additional-Currency Net Change$G_L Entry$SUM$Additional-Currency Amount/,0.0) AS /Additional-Currency Net Change/
,ISNULL(/SUB$Add_-Currency Balance at Date/./Add_-Currency Balance at Date$G_L Entry$SUM$Additional-Currency Amount/,0.0) AS /Add_-Currency Balance at Date/
,ISNULL(/SUB$Additional-Currency Balance/./Additional-Currency Balance$G_L Entry$SUM$Additional-Currency Amount/,0.0) AS /Additional-Currency Balance/

FROM /Demo Database BC (21-0)/.dbo./CRONUS UK Ltd_$G_L Account$437dbf0e-84ff-417a-965d-ed2bb9650972/ AS /G_L Account/ WITH(READUNCOMMITTED)

OUTER APPLY (
SELECT TOP (1) SUM(/Net Change$G_L Entry/./SUM$Amount/) AS /Net Change$G_L Entry$SUM$Amount/
FROM /Demo Database BC (21-0)/.dbo./CRONUS UK Ltd_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972$VSIFT$Key2/ AS /Net Change$G_L Entry/ WITH(READUNCOMMITTED,NOEXPAND)
WHERE (/Net Change$G_L Entry/./G_L Account No_/=/G_L Account/./No_/)) AS /SUB$Net Change/

OUTER APPLY (
SELECT TOP (1) SUM(/Balance$G_L Entry/./SUM$Amount/) AS /Balance$G_L Entry$SUM$Amount/
FROM /Demo Database BC (21-0)/.dbo./CRONUS UK Ltd_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972$VSIFT$Key2/ AS /Balance$G_L Entry/ WITH(READUNCOMMITTED,NOEXPAND)
WHERE (/Balance$G_L Entry/./G_L Account No_/=/G_L Account/./No_/)) AS /SUB$Balance/

OUTER APPLY (
SELECT TOP (1) /Account Subcategory Descript_$G_L Account Category/./Description/ AS /Account Subcategory Descript_$G_L Account Category$LOOKUP$Description/
,/Account Subcategory Descript_$G_L Account Category/./Entry No_/ AS /Account Subcategory Descript_$G_L Account Category$Entry No_/
FROM /Demo Database BC (21-0)/.dbo./CRONUS UK Ltd_$G_L Account Category$437dbf0e-84ff-417a-965d-ed2bb9650972/ AS /Account Subcategory Descript_$G_L Account Category/ WITH(READUNCOMMITTED)
WHERE (/Account Subcategory Descript_$G_L Account Category/./Entry No_/=/G_L Account/./Account Subcategory Entry No_/)
ORDER BY /Account Subcategory Descript_$G_L Account Category$Entry No_/ ASC) AS /SUB$Account Subcategory Descript_/

OUTER APPLY (
SELECT TOP (1) SUM(/Debit Amount$G_L Entry/./SUM$Debit Amount/) AS /Debit Amount$G_L Entry$SUM$Debit Amount/
FROM /Demo Database BC (21-0)/.dbo./CRONUS UK Ltd_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972$VSIFT$Key2/ AS /Debit Amount$G_L Entry/ WITH(READUNCOMMITTED,NOEXPAND)
WHERE (/Debit Amount$G_L Entry/./G_L Account No_/=/G_L Account/./No_/)) AS /SUB$Debit Amount/

OUTER APPLY (
SELECT TOP (1) SUM(/Credit Amount$G_L Entry/./SUM$Credit Amount/) AS /Credit Amount$G_L Entry$SUM$Credit Amount/
FROM /Demo Database BC (21-0)/.dbo./CRONUS UK Ltd_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972$VSIFT$Key2/ AS /Credit Amount$G_L Entry/ WITH(READUNCOMMITTED,NOEXPAND)
WHERE (/Credit Amount$G_L Entry/./G_L Account No_/=/G_L Account/./No_/)) AS /SUB$Credit Amount/

OUTER APPLY (SELECT TOP (1) SUM(/Balance at Date$G_L Entry/./SUM$Amount/) AS /Balance at Date$G_L Entry$SUM$Amount/
FROM /Demo Database BC (21-0)/.dbo./CRONUS UK Ltd_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972$VSIFT$Key2/ AS /Balance at Date$G_L Entry/ WITH(READUNCOMMITTED,NOEXPAND)
WHERE (/Balance at Date$G_L Entry/./G_L Account No_/=/G_L Account/./No_/)) AS /SUB$Balance at Date/

OUTER APPLY (
SELECT TOP (1) SUM(/Additional-Currency Net Change$G_L Entry/./SUM$Additional-Currency Amount/) AS /Additional-Currency Net Change$G_L Entry$SUM$Additional-Currency Amount/
FROM /Demo Database BC (21-0)/.dbo./CRONUS UK Ltd_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972$VSIFT$Key2/ AS /Additional-Currency Net Change$G_L Entry/ WITH(READUNCOMMITTED,NOEXPAND)
WHERE (/Additional-Currency Net Change$G_L Entry/./G_L Account No_/=/G_L Account/./No_/)) AS /SUB$Additional-Currency Net Change/

OUTER APPLY (
SELECT TOP (1) SUM(/Add_-Currency Balance at Date$G_L Entry/./SUM$Additional-Currency Amount/) AS /Add_-Currency Balance at Date$G_L Entry$SUM$Additional-Currency Amount/
FROM /Demo Database BC (21-0)/.dbo./CRONUS UK Ltd_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972$VSIFT$Key2/ AS /Add_-Currency Balance at Date$G_L Entry/ WITH(READUNCOMMITTED,NOEXPAND)
WHERE (/Add_-Currency Balance at Date$G_L Entry/./G_L Account No_/=/G_L Account/./No_/)) AS /SUB$Add_-Currency Balance at Date/

OUTER APPLY (SELECT TOP (1) SUM(/Additional-Currency Balance$G_L Entry/./SUM$Additional-Currency Amount/) AS /Additional-Currency Balance$G_L Entry$SUM$Additional-Currency Amount/
FROM /Demo Database BC (21-0)/.dbo./CRONUS UK Ltd_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972$VSIFT$Key2/ AS /Additional-Currency Balance$G_L Entry/ WITH(READUNCOMMITTED,NOEXPAND)
WHERE (/Additional-Currency Balance$G_L Entry/./G_L Account No_/=/G_L Account/./No_/)) AS /SUB$Additional-Currency Balance/ WHERE (/G_L Account/./No_/>@0) ORDER BY /No_/ ASC OPTION(FAST 50)',@0=N'1000'

Hi Guys,

just in case it helps someone else out?

We found these.

Also, you can just comment out the "set @p1=96" and the traced query will run right on top of BC.

It's pretty interesting actually.

Raul found this.

I know scenarios where cross or outer apply are good to use simply for row by row processing or sometimes performance, but I searched and here are the results that might answer the use of outer apply in those queries instead of left join.

Here are some reasons why OUTER APPLY might be used instead of LEFT JOIN:

Row-by-row processing: OUTER APPLY can be used to invoke a table-valued function for each row of the left table, which can be useful for complex calculations or transformations that need to be applied row-wise.
Derived tables with aggregates: It can be used to create derived tables that contain aggregated data without the need for a group by clause, which can simplify queries.
Correlated subqueries: OUTER APPLY can be used for correlated subqueries where the derived table needs to reference columns from the outer query.
Performance: In some cases, OUTER APPLY can offer better performance than a LEFT JOIN, especially when the function or derived table in the APPLY part is complex.
Flexibility: OUTER APPLY provides a level of flexibility that LEFT JOIN does not, particularly when dealing with dynamic sets of columns or when the joined table’s columns depend on the rows from the left table.

OUTER APPLY can be beneficial in scenarios where more complex data retrieval is necessary, such as when dealing with hierarchical data, dynamic columns, or when needing to perform operations unique to each row of the primary table

https://dba.stackexchange.com/questions/137546/comparing-left-join-and-outer-apply-doing-the-same-thing

https://dba.stackexchange.com/questions/75048/outer-apply-vs-left-join-performance

https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-468-understanding-and-resolving-the-quot-could/ba-p/4022046

https://www.mssqltips.com/sqlservertip/7491/prepare-sql-statement-spprepare-spexecute/

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-prepare-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-prepexec-transact-sql?view=sql-server-ver16