Error message: 1:N Fetch Mode is not allowed on Datasource having join condition with node other than its immediate parent

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 are using a field as join-condition field from 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.