How to create Database View to join cert_element and incident table?

[Dennis25]

Hello,

I have set up Data Certification for certain fields from Incident table.

As you can see below, by reporting only on cert_element table, I am not able to add in more fields from incident table (i.e: incident.priority, incident.assignment group, incident.service_offering).

So I thought of creating a Database View to join cert_element and incident table to get the fields I need from incident table. 

Which fields do I use to join cert_element table and incident table using Database View?

I have tried as below.

But getting error that ce.id is unknown.

I have checked in the cert_element table that it should be there.

ce.id is actually as below:

Any ideas here are greatly appreciated.

Thanks in advance.

[Ankur Bawiskar]

Hi,

Did you add id field as view field for that cert_element table?

Are you sure that field is holding incident sysId?

Regards
Ankur

[Ankur Bawiskar]

Hi,

check this out

Database view - How to correctly join a table using a document_id field

Regards
Ankur

[Ankur Bawiskar]

@Dennis 

Did you mistakenly marked other response as correct?

The link I shared already provided the same solution.

Please mark appropriate response as correct based on timely response.

Regards
Ankur

[Aman Kumar S · Accepted solution]

Hey Dennis,

Found one related article on HI:

Description

When creating a database view it is sometimes necessary to join tables via a Document ID type field.

Like Reference fields, Document ID fields allow the record to reference another record. However, Document ID fields allow you to reference a record on any table, not just a pre-defined one like Reference fields.

This means that the data is stored in 2 fields: Table Name & Document (sys_id)

Incorrectly joining tables by only referencing the Document field can lead to unexpected outcomes like only returning partial results or, a mismatch between the row count and the results returned.

Ref link:

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0952022