1 Topic by carlo_politi

  • carlo_politi
  • Veteran
  • Offline
  • Registered: 25-01-2013
  • Posts: 147

Topic: report error

good day, other question, related this time to report. i'm trying to execute the "Survey Details" from Report Browser in Datawarehouse, selecting the survey, enabling the vague date and final to download select but i get this error:

general_errors.There was an SQL error: ERROR: cannot update view "cache_occurrences" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule. - UPDATE "cache_occurrences" SET "downloaded_flag" = 'F' WHERE ("id" IN ('5169','5121','5122','5170','5123','5171','5124','5172','5125','5173','5126','5174','5127','5128','5129','5130','5131','5133','5134','5135','5136')) AND "downloaded_flag" = 'I'

is there anything i could fix on my own or any clues to make it work?

thanks

Carlo Politi - Software Engineer
QuestIT s.r.l.
Home page: www.quest-it.com
E-Mail: politi@quest-it.com - Skype: carlo.politi.questit

2 Reply by Jim Bacon

  • Jim Bacon
  • Veteran
  • Offline
  • From: BRC
  • Registered: 03-11-2008
  • Posts: 980

Re: report error

Hi Carlo

I can maybe understand something of this error message. An update is being attempted to mark a record as downloaded by changing the downloaded_flag from I (Initial) to F (Final). It is attempting to do this on cache_occurrences. Once, long ago, this was a table but it has been replaced by cache_occurrences_functional and cache_occurrences_nonfunctional. For backwards compatibility with old reports, the cache_occurrences table was replaced by a view of the same name.

While cache_occurrences was a table this would have worked; now it is a view it will not.

So far as I can see, this field is not in the new cache tables and only in the occurrence table. I think you have found a bug in the function updateDownloaded() of \application\libraries\ReportEngine.php which attempts this update.

I have raised an issue, https://github.com/Indicia-Team/warehouse/issues/275

I imagine the resolution will be to remove the following statement from the function

    $response = $db->in("id", $idList)->where("downloaded_flag", ($mode == 'FINAL' ? 'I' : 'N'))
      ->update('cache_occurrences',
          array('downloaded_flag' => ($mode == 'FINAL' ? 'F' : 'I')));

Since I have never worked in this area of code it may not be quite as simple as that.

Jim Bacon.