Database Views - No Join for Table Error

[The SN Nerd]

I am creating a database view to join child & Parent CI's based off their relationship.

The table structure is as follows:

CI > Version Controlled CI > Business Objective

CI > Version Controlled CI > Requirement

I need to show a mix of fields from both the 'Version Controlled CI' table and the child tables (Business Objective, Requirement)

This works fine when I create View Tables on 'Version Controlled CI', but then I can't add fields that exist on the child tables 'Business Objective' or 'Requirement'.

When I change the Parent and Child View Tables to there actual tables (Business Requirement and Requirement respectively) an error is thrown.

The log file says "Logic Error: No Join Found for table: u_cmdb_ci_version_controlled: no thrown error"

In the UI:

Syntax Error or Access Rule Violation detected by database (Unknown column 'bizobju_cmdb_ci_version_controlled.u_version' in 'field list')

u_version exists on the 'Version Controlled CI' table.

If I remove all fields on the 'Version Controlled CI' table, it works fine.

If I use 'Business Objective' instead of 'Version Controlled CI' table, I get this error:

Syntax Error or Access Rule Violation detected by database (Not unique table/alias: 'cmdb_ci_version_controlled')

I can't see what I am doing wrong here. Any ideas?

[tanumoy]

Most probably the fields you used in the where clause are not present in the View Fields of the View Table.

Could you please provide some screenshots for better understanding.

[The SN Nerd]

The only fields referenced in the where clause is sys_id, which is present.

The Access Rule violation field only shows If the fields are included in the view table.

I can't simply remove them because I need those fields.

I've gone through every Access Rule violation thread on this forum and no solutions have prevailed or match the scenario I am facing.

[The SN Nerd · Accepted solution]

This was solved by using dots (.) instead of underscores between the variable prefix and field names.

Underscores seem to work fine until you reference fields on parent tables.