Finance | Project Operations, Human Resources, ...

Entity view with duplicate sub views

(1) Share

Report

Posted on by Jan M.

3,335

We are encountering a problem with the entity FinancialDimensionValues. On database there is an error while selecting data: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

The reason comes with the sub view FinancialDimensionValueEntityExistingListView. Looking on the SQL create script we see several entries for the same Dim View:

..
UNION ALL
SELECT T.NAME
FROM DimAttributeInventTable T
WHERE T2.VIEWNAME = 'DimAttributeInventTable'
AND T.RECID = T1.ENTITYINSTANCE
..

We could not find extensions on the objects. Deleting the view and full sync does create it with the same structure.

The view seems to be generated with all the sub views in a process. Which finance process could that be?

Thanks for any help.

All responses (2)

Answers (0)

Sort by

Suggested answer

Saif Ali Sabri 2,820 Super User 2026 Season 2 on at

Like (0)

Report
Copy link

Link copied!
The SQL view is trying to pull multiple entries for the same dimension value. This happens if, in one of the dimension attribute views (like DimAttributeInventTable), there are multiple records for the same entity instance.

Check for Duplicate Dimension Records: Look in DimAttributeInventTable and other dimension tables (e.g., DimAttributeMainAccount, etc.) for any duplicate records based on the ENTITYINSTANCE field. Cleaning up these records may resolve the issue.

Verify Active Dimensions Configuration: Ensure that your active dimension settings do not have overlapping or duplicate dimensions that could cause conflicts in the view generation.

Re-run Full Synchronization: Even though you’ve deleted and re-synced the view, try re-running a full synchronization specifically targeting dimensions, as it can help refresh the entire dimension framework, including any dependent views.

if it’s not visible as an extension, may be affecting the generated SQL view behavior.
Suggested answer

Saif Ali Sabri 2,820 Super User 2026 Season 2 on at

Like (1)

Report
Copy link

Link copied!
Duplicate Entries Due to UNION ALL:

UNION ALL is used to combine the results of multiple SELECT statements without removing duplicates. If there are multiple SELECT statements targeting the same source (e.g., DimAttributeInventTable) and they’re not sufficiently filtered, it can lead to duplicates.

Consider Using DISTINCT:

Alternatively, you could add a DISTINCT keyword in the main SELECT clause of FinancialDimensionValueEntityExistingListView. This can help filter out duplicates without needing to modify each UNION ALL block individually, though it might affect performance depending on the data volume.