Is it possible to use Document ID fields in joins for Database Views?

[Andy Hopper]

Does anyone know if it's possible to use Document ID fields in joins on Database Views?  (I know that Doc IDs behave differently to Reference fields)

I'm trying to join the sys_update_xml table (cu) to sys_update_version table (ver) WHERE cu_sys_id = ver_source.

A bit of google searching suggests this is possible from one of Tim Woodruff and Martin Wood's books, but it's not working for me.

My view config is shown below.  It just returns an empty list.  Reporting on either of the tables individually returns all records and fields successfully.

[Ankur Bawiskar]

Hi,

I won't recommend having database view on those 2 tables since those are huge tables

What is your requirement here?

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[Andy Hopper]

Hi Ankur,

Ignore the reason/requirement for now, I'm just curious to know if it's possible.

(It's not for production and won't be run frequently, so it doesn't need to be performant. Plus, once working I will add filters to exclude anything in the Default update set and where version state is not 'current'.)

[Adam Stout]

There are several OOTB views that join on Document ID (see incident_sla and incident_metric as an example).  You just want to join with the table field as well.  

That being said, what is the use case for joining these two tables?  What are you trying to get to?  Perhaps there is another way.

[Andy Hopper]

Thanks for your response Adam.

It looks like incident_sla actually joins on a reference field, but I can see that incident_metric joins on a DocID as you say.

I'm not sure what you mean by 'join with the table field as well' though?  Can you give an example?  It doesn't look (to me) like the incident_metric view is doing anything differently; it's filtering the first table by 'table = incident' - is that what you meant?  In which case, I don't have that field available to filter on with these particular tables.

The following is unimportant because my primary reason for asking this question is that I wanted to know if joining on a DocID is possible (and how to do it) - but seeing as you asked...

The use case is to produce a baselined list of non-OOTB configuration changes made to the platform.

i.e. show everything in the Customer Updates table that:

- Is not in the default update set

- Was created/changed by an employee (not ServiceNow)

- Is the current version (sys_update_version.state = 'current')

- Plus various fields from the associated Update Sets like description/comments etc.

If a DB View isn't practical then I'll do it with a script.