Error Message: '1:N Fetch Mode Is not Allowed on Datasource Having Join Condition with Node Other than its Immediate Parent'

The following error message is sometimes displayed when running a report:

Error

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

The error occurs if the Query contains two or more data source structures and if one of the data source structures is using a field as a joint condition from the other data source structure.

This error occurs only if 1:N is used as Fetch mode/Fetch.

The following is an example containing two data source structures:

A. Containing the data sources/tables CustTable and SalesTable

B. Containing the data sources/tables CustTable, DlvTerm, and LanguageTxt

The data source/table LanguageTxt in B is using a field as a joint-condition field from A which is causing the error message (error is only displayed if 1:N is used in the Fetch mode/Fetch).

The following error is displayed when running the Query report:

Solutions

The following solutions can be implemented to fix the error:

Fix the Issue by Changing the Query Structure

The 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, such as the following:

Fix the Issue by Changing from 1:N to 1:1

Change from 1:N to 1:1 in the Fetch Mode/Fetch, but this could cause the following error if many tables are joined together:

Error

[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 displayed if the query contains many data sources/tables and why Fetch Mode/Fetch 1:1 will work in most cases.

Fix the Issue in Lasernet FO Connector version 6 by Adding a Range instead of a Filter

Lasernet FO Connector 6 contains the option to add either a Filter or a Range in the Query wizard.

Adding a Range will fix the issue (Filter = No):

Adding a Filter will display the same error:

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 an SQL statement such as the following:  

This is a single SQL statement and why the error isn't thrown when using Fetch Mode/Fetch 1:1. It is 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 is NOT possible to use a Filter and/or condition field from one of the other data-source-structure, separate SQL statements are being executed: