5 Replies Latest reply on Jun 11, 2015 8:47 AM by Mike_Mitchell

    Odd ESS behavior - failure to return correct records on search

    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:

       

      SELECT '',

               b.owner,

               b.table_name,

               b.column_name,

               b.position,

               b.constraint_name

          FROM ALL_CONSTRAINTS a, ALL_CONS_COLUMNS b

         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?