Database view with a three table join

[ayman_h]

Hi,

I am trying to create a database view that will give me all the SLAs linked to Incidents, where the Incident doesn't have an Incident Task related to it. 

I am struggling trying to do this using a join.

Does anyone have an idea how I can do this?

Thanks,

Ayman

[AshishKM]

Checked again with more incident_task & incident mapping records .. and its failed.

somehow its working for single incident_task record, when created more incident_task .. it's multiplying the count base on total incident count... it's just adding incident_task with all incident.

@AndersBGS , please help for this left join case. 

[AndersBGS]

Hi @ayman_h ,

you can build a database view based on incident table where incident task and task_sla is related to with no in left join.

this should resolve your query

if my answer has helped with your question, please mark my answer as accepted solution and give a thumb up.

Best regards

Anders

[-O- · Accepted solution]

I had some time finally to look into the problem and below you'll find how I solved this in my PDI.

This is how the definition of the database view looks like:

The where clause for table task_sla is:

not exists (select 1 from `task` inct where `inct`.`sys_class_name` = 'incident_task' and `inct`.`a_ref_1` = `inc`.`sys_id`)

The where clause for table incident is:

inc.sys_id = tsla.task

The key part of the solution is finding the storage alias for field incident on table incident_task - a_ref_1 in the where clause for table task_sla.

For this one should open table Storage Column Aliases (sys_storage_alias) and filter it down to where Table (table_name) is incident_task and Element name (element_name) is incident:

 Whatever is in column Storage alias (storage_alias) - a_ref_1 in my PDI - should be used.

(B.t.w. all the back-ticks are not really necessary, not exists (select 1 from task inct where inct.sys_class_name = 'incident_task' and inct.a_ref_1 = inc.sys_id) works just fine).

[-O-]

Anchor template to find the storage alias:

https://<instance name>.service-now.com/sys_storage_alias_list.do?sysparm_query=table_name%3Dincident_task%5Eelement_name%3Dincident

All this is because there is no real table in the SN database called incident_task - unless the default extension model for task has been modified to Table per class.

And - very important - when moving the database view to "upper" instances, you need to make sure the real name for reference incident (on incident_task) is/remains the same - in my case a_ref_1.

[ayman_h]

Excellent, that works! I wasn't aware of the Storage alias table. Its called 'a_ref_1' in my instance so looks like thats the default and shoudn't be an issue in other instances.