Database View where clause complex query

[ajitmundhekar]

Hi All,

I am trying to query a related field of CI Relationship table in Database view where clause.

For eg:

cir_parent = iar_u_infrastructure_ci && cir_parent.sys_class_name = 'cmdb_ci_server'

But it is returing error message as:

Syntax Error or Access Rule Violation detected by database (Unknown column 'cir_parent' in 'on clause')

However, first part of my where clause i.e. (cir_parent = iar_u_infrastructure_ci) is working without any issues.

TIA

[LaurentChicoine]

You cannot use dot walking on database view. This means you would have to add a new joined table (cmdb_ci_server) with a on clause of cir_parent = serveralias_sys_id.

[Narasimhulu2]

HI Laurent,

Could you please let me know if the above process is not working then ...how to achieve!!.

In my case both table have dot walk fields only.

EX: Table1.calss.business_application =Table2.task.busniess_application 

Thank you in advance 🙂

[LaurentChicoine]

In your case you would probably need the following views:

• Table1
• Table2
• calss
• task

Join calss with task where calss.business_application = task.business_application

Join Table1 with calss where Table1.calss = calss.sys_id

Join Table2 with task where Table2.task = task.sys_id

I haven't tested this, you might need to have some view using a left join based on your data.