Database View not working after upgrade

Sue Frost · ‎04-12-2017

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 · ‎04-12-2017

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.

View solution in original post

Mwatkins · ‎04-12-2017

The fact that there is only one query in Istanbul is revealing. The first query could be a COUNT(*) operation and would tell the platform if there are any results to be displayed. If there aren't any results to be displayed then the second query never fires. You can hover over the "..." to see what is being selected in the SELECT clause.

I notice that the final apostrophe is missing from the debug statement you posted from Istanbul. Was that the whole statement?

Also, can you paste the encoded query and SQL debug output that you get when going directly to the table and matching the same criteria? There must be something different about how that is running.

Sue Frost · ‎04-12-2017

That final apostrophe? Just my clumsy fingers. It's there in the actual statement.

Encoded query when looking directly at the u_attachments table:

- Geneva: sys_created_onONToday@javascript:gs.daysAgoStart(0)@javascript:gs.daysAgoEnd(0)^u_taskSTARTSWITHcase

- Istanbul: sys_created_onONToday@javascript:gs.daysAgoStart(0)@javascript:gs.daysAgoEnd(0)^u_taskSTARTSWITHcase

I've attached the SQL output when going directly to the table.

When I hover over the /*...*/ in the debug log, I see this:

Geneva:

Istanbul:

Mwatkins · ‎04-12-2017

I was thinking of the ellipses in between the words SELECT ... FROM. That one should allow hovering too. It should either show a list of fields or COUNT(*)

Sue Frost · ‎04-12-2017

Ah.

The first select in Geneva (and only select in Istanbul shows the count: count(*) AS recordcount

The second select in Geneva shows the fields:

Mwatkins · ‎04-12-2017

Okay, this is definitely a case of the data being different some way in your two environments. It could be a difference in a number of places:

There may be no u_attachments records that are active
No u_case, u_case_task or u_upcoming_abeyances records may be referenced by a u_attachments.u_task field and their u_entity field must be u_case.
The records in #2 also need to have some u_communications record that has as them as the u_case field
The number field of the records in #2 must start with CASE