AnsweredAssumed Answered

Odd ESS behavior - failure to return correct records on search

Question asked by Mike_Mitchell Expert on Jun 11, 2015
Latest reply on Jun 11, 2015 by Mike_Mitchell

Good day, all. I have an older system (FM 11) connected to an Oracle view (11g) via ESS. Recently, it has started displaying some very odd behavior vis a vis searching. I can view records normally in the FileMaker database, and I can see the records that exist properly (verified with the Oracle DBA). However, when I perform a search in FileMaker, I get incorrect or unexpected results. Examples:


1) Searching for a date that exists returns "No records match".


2) Searching for "<" date returns correct results.


3) Searching for a range of dates using "..." returns an inaccurate range, where it excludes the date on the right-hand side of the range. So if, for example, I use 9/10/2013...9/13/2013, I get everything from 9/10 through 9/12.


The date field seems to be the only problem child. It's stored as a timestamp in the Oracle system, but FileMaker interprets it as a date. I've re-synced the table a couple of times, to no effect.


I asked the DBA to look in the logs and tell me if she could identify the query FileMaker is submitting, thinking that might shed some light. That's where it got really weird. This is what she said she saw:









   WHERE     (    UPPER (b.table_name) = UPPER ('E_COCS')

              AND UPPER (b.owner) = UPPER ('EMBOS'))

         AND (    UPPER (a.table_name) = UPPER ('E_COCS')

              AND UPPER (a.owner) = UPPER ('EMBOS')

              AND a.constraint_type = 'P')

         AND (a.constraint_name = b.constraint_name)

ORDER BY b.owner, b.table_name, b.position


The only part of that query that has anything to do with the view involved is "EMBOS" (which is the name of the Oracle database). The column I'm searching against is START_DT.


I'm kinda stumped here. There was an update to the Oracle database in between the time this was working and now, but the developer swears nothing about the view, the index, or the schema changed.


Anybody familiar enough with ESS / Oracle connections to shed any light on this?