This article discusses limits of SQL generation for lookup fields and describes the possible solution.
When SQL for data structure is generated and the data structure contains a lookup field, if the lookup field value data structure root data structure entity is of the same name as one of the main data structure entities, renaming is applied. This renaming involves adding the prefix lookup to all data structure entities in the lookup value data structure.
If the lookup used column from the root data structure entity, the SQL should be generated properly (including localized fields), but if there is a field from joined data structure entity, the generated multi-part identifier doesn’t contain the lookup prefix in the table part. To solve this problem the model has to be adjusted, namely the lookup value data structure. If the root data structure entity is named in a way that it doesn’t match the data structure entities of the main data structure (using lookup prefix is considered as a best practice), the renaming won’t be applied and the generated SQL will be correct.