I have for 4 tables in ServiceNow
cert_audit
cert_audit_result
u_worklog (Custom table)
u_m2m_worklogs_configuration (M2M table from cmdb_ci to u_worklog)
In SQL I would have created bellow kind of a query as a view to be used by users
SELECT car.configuration_item,
ca.audit,
car.status,
COALESCE(temp.u_audit_review_response,'Review missing')
FROM cert_audit ca
--Get latest audit record
JOIN cert_audit_result car
ON ca.last_run_date <= car.sys_created_on AND ca.sys_id=car.audit
--Get all all Worklogs related to CI and Audit if exists
LEFT JOIN (
SELECT m2mwc.u_configuration_item,w.u_audit,w.u_audit_review_response
FROM u_worklog w
JOIN u_m2m_worklogs_configuration m2mwc
ON w.sys_id = m2mwc.u_worklog AND w.u_type = 'Audit review'
) temp
ON ca.audit = temp.audit AND car.configuration_item = temp.u_configuration_item
So basically want to report latest audits for all CIs and status of worklog (if available) associated for the audit.
First I was thinking to have database view pointing to another database view, but it seems that is not possible.
