Database View not working after upgrade

[Sue Frost]

We're upgrading from Geneva to Istanbul.

A database view that hasn't been changed in over two years is now not returning any results. I've confirmed that there are no differences between the version in our Geneva instance and the one in our Istanbul instance.

I can't find any release notes for Helsinki or Istanbul to indicate there were changes to database views. There are records that should be showing in both instances.

If someone could point me in the right direction or offer an explanation, that would be very much appreciated.

[Mwatkins]

One thing you might investigate is to use the LEFT JOIN option in the database view in the case that you want both matching and non-matching records to be returned from the left side of the join.

[Sue Frost]

Yup. I would completely agree with that. Except when I go to the u_attachments table and validate all those criteria ...

Geneva:

and Istanbul

I am hesitant to make any changes until I understand what in the upgrade caused the functionality to change.

[Mwatkins · Accepted solution]

Ahh... here's the problem. In your Istanbul instance there are no communication records related to the CASE records.

/u_attachments_list.do?sysparm_query=u_active%3Dtrue%5Eu_task.sys_class_name%3Du_case%5EORu_task.sys_class_name%3Du_upcoming_abeyances%5EORu_task.sys_class_name%3Du_case_task%5Eu_task.numberSTARTSWITHCASE%5Eu_task.ref_u_case.u_entity%3Du_case&sysparm_first_row=1&sysparm_view=

/u_communications_list.do?sysparm_list_mode=grid&sysparm_query=u_case%3Dd6caae844f79ba84e058d0fca310c7f0%5EORu_case%3Deed9a78e4f46fe004e374ebf9310c715%5EORu_case%3Dfce52c5a4fc6f6805a1e4bff9310c718&sysparm_offset=

It is an inner join, in the database view so if any JOIN fails to return a result then no rows will show up in the UI. Try changing the JOIN type on the u_communications join to a LEFT JOIN.

P.S. Only the condition for number starts with "CASE" is in the SQL. Not the one for "CSTSK" like in your screenshot.

[Sue Frost]

I tried creating a Case record with a communication records and attachments.

I added some attachments directly to the Case as well.

And dagnamit, it worked.

I still don't quite understand because I think that any attachments related to a Case should show AND any attachments related to a COMM record, but this code hasn't changed since our go-live and no-one has complained about what they're seeing.

Thanks for all your help on this nonsense.

[amlanpal]

Hi Sue,

This is a Read ACL issue. From Fuji onward you need to provide separate access via ACL on the database views. Please find 1.1 in the link Database Views - ServiceNow Wiki .

Also earlier, I have answered similar question in this thread where the issue was because of the Rad ACL. Please refer: Unable to see incident_sla in tables but can see it in reports?

I hope this helps.Please mark correct/helpful based on impact