Error message: 1:N Fetch Mode is not allowed on Datasource having join condition with node other than its immediate parent
K
Kim Franck Agerholm
started a topic
about 1 year ago
The error message: 1:N Fetch Mode is not allowed on Datasource having join condition with node other than its immediate parent are sometimes thrown when running a report.
The error is thrown if the Query contains two or more data-source-structures and if one of the data-source-structure are using a field as join-condition from the other data-source-structure.
This error is only thrown if 1:N is used as Fetch mode/Fetch.
The following is an example containing two data-source-structures
A. Containing the datasources/tables CustTable and SalesTable
B. Containing the datasources/tables CustTable, DlvTerm and LanguageTxt
The data-source/table LanguageTxt in B are using a field as join-condition field from A which is causing the error message. (error is only thrown if 1:N is used in the Fetch mode/Fetch)
The following error is thrown when running the Query-report
1. Fix issue by changing the query-structure
Best approach for fixing the issue is restructuring the Query-structure as it should be possible to have all the required data-sources within the same data-source structure like the following.
2. Fix the issue by changing from 1:N to 1:1
Another option for fixing the issue is changing from 1:N to 1:1 in the Fetch Mode/Fetch, but this could cause the following error in case a lot of tables are joined together.
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot create a row of size 8061 which is greater than the allowable maximum row size of 8060
This error is only thrown if the query contains a lot of data-sources/tables and why Fetch Mode/Fetch 1:1 works in most cases.
3. Fix the issue in LAC365FO version 6 by adding a Range instead of a Filter
LAC365FO version 6 contains the option to add either a Filter or a Range in the Query-wizard.
Adding the a Range would fix the issue (Filter = No)
Adding a Filter would thrown the same error Query filter cannot be specified on a query that are split into multiple queries dues to 1:n fetch mode.
This is happening as a Filter is used for the complete result-set.
Fetch Mode / Fetch 1:1 are creating the following SQL-statement like the following - This is a single SQL-statement and why the error isn't thrown when using Fetch Mode/Fetch 1:1
It's possible to add the Filter to the complete result-set as this is a single SQL-statement.
Fetch Mode / Fetch 1:N are creating the following multiple and separate SQL-statements and why it's NOT possible to use a Filter and/or condition-field from one of the other data-source-structure.
Simple as it's separate SQL-statements getting executed.
Kim Franck Agerholm
The error message: 1:N Fetch Mode is not allowed on Datasource having join condition with node other than its immediate parent are sometimes thrown when running a report.
The error is thrown if the Query contains two or more data-source-structures and if one of the data-source-structure are using a field as join-condition from the other data-source-structure.
This error is only thrown if 1:N is used as Fetch mode/Fetch.
The following is an example containing two data-source-structures
A. Containing the datasources/tables CustTable and SalesTable
B. Containing the datasources/tables CustTable, DlvTerm and LanguageTxt
The data-source/table LanguageTxt in B are using a field as join-condition field from A which is causing the error message. (error is only thrown if 1:N is used in the Fetch mode/Fetch)
The following error is thrown when running the Query-report
1. Fix issue by changing the query-structure
Best approach for fixing the issue is restructuring the Query-structure as it should be possible to have all the required data-sources within the same data-source structure like the following.
2. Fix the issue by changing from 1:N to 1:1
Another option for fixing the issue is changing from 1:N to 1:1 in the Fetch Mode/Fetch, but this could cause the following error in case a lot of tables are joined together.
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot create a row of size 8061 which is greater than the allowable maximum row size of 8060
This error is only thrown if the query contains a lot of data-sources/tables and why Fetch Mode/Fetch 1:1 works in most cases.
3. Fix the issue in LAC365FO version 6 by adding a Range instead of a Filter
LAC365FO version 6 contains the option to add either a Filter or a Range in the Query-wizard.
Adding the a Range would fix the issue (Filter = No)
Adding a Filter would thrown the same error Query filter cannot be specified on a query that are split into multiple queries dues to 1:n fetch mode.
This is happening as a Filter is used for the complete result-set.
Fetch Mode / Fetch 1:1 are creating the following SQL-statement like the following - This is a single SQL-statement and why the error isn't thrown when using Fetch Mode/Fetch 1:1
It's possible to add the Filter to the complete result-set as this is a single SQL-statement.
Fetch Mode / Fetch 1:N are creating the following multiple and separate SQL-statements and why it's NOT possible to use a Filter and/or condition-field from one of the other data-source-structure.
Simple as it's separate SQL-statements getting executed.