Share
Report
151Hi All,
I would be very appreciative if someone could please point me to a detailed discussion/documentation on how BC resolves the SQL being sent into it via OData query.
Specifically how the where clauses are applied and when they are applied.
We are learning how to query BC on prem / cloud using both PowerBI and the ODBC Driver for Dynamics 365 from CData.
We sent a query for Chart_of_Accounts into OData using the CData ODBS Driver selecting account 1000.
We logged the SQL using SQL Server Profiler.
And there was no where clause applied in the underlying SQL sent into SQL Server.
At least we didn't see that.
I believe that filters can be applied on the query string for the odata query but it appears that the CData driver does not do this.
I am sure that someone has written detailed documentation on this but I could not find it when I searched.
So any pointer would be greatly appreciated.
I have put the SQL that got generated below. It appears that the APIs that expose data out to OData have quite a lot of logic built into them to present exactly what is supposedly desired.
It is not simply dumping the GL Account Table.
Thank you in advance for any assistance you may be willing to offer us.
Best Regards
Peter
SELECT TOP (50) /G_L Account/./timestamp/ AS /timestamp/,/G_L Account/./No_/ AS /No_/,/G_L Account/./Name/ AS /Name/,/G_L Account/./Account Type/ AS /Account Type/,/G_L Account/./Account Category/ AS /Account Category/,/G_L Account/./Income_Balance/ AS
/Income_Balance/,/G_L Account/./No_ 2/ AS /No_ 2/,/G_L Account/./Direct Posting/ AS /Direct Posting/,/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/./VAT Bus_ Posting Group/ AS /VAT Bus_ Posting Group/,/G_L Account/./VAT Prod_ Posting Group/ AS /VAT Prod_ Posting Group/,/G_L Account/./Default IC Partner G_L Acc_ No/ AS /Default IC Partner G_L Acc_ No/,/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/./$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/ ORDER BY /No_/ ASC OPTION(FAST 50)
SELECT TOP (50) /G_L Account/./timestamp/ AS /timestamp/,/G_L Account/./No_/ AS /No_/,/G_L Account/./Name/ AS /Name/,/G_L Account/./Account Type/ AS /Account Type/,/G_L Account/./Account Category/ AS /Account Category/,/G_L Account/./Income_Balance/ AS
/Income_Balance/,/G_L Account/./No_ 2/ AS /No_ 2/,/G_L Account/./Direct Posting/ AS /Direct Posting/,/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/./VAT Bus_ Posting Group/ AS /VAT Bus_ Posting Group/,/G_L Account/./VAT Prod_ Posting Group/ AS /VAT Prod_ Posting Group/,/G_L Account/./Default IC Partner G_L Acc_ No/ AS /Default IC Partner G_L Acc_ No/,/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/./$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/ ORDER BY /No_/ ASC OPTION(FAST 50)
Categories:
I have the same question (0)
All responses (
Answers (
