2 Replies Latest reply on Nov 8, 2013 9:46 AM by Jim Smith

    Why do i get different results in SQL Developer vs Enterprise Manager with the same query?

    user1138166

      When using UPPER and/or TO_DATE within the WHERE condition i get different results in SQL Developer vs SQL Scratchpad in Enterprise Manager?

       

      I wont go into details about how much time this SQL Developer BUG caused me, but it makes me call into question all things oracle. Have not seen this in MSSQL!

       

      Anyhow, this is the WHERE clause:

       

        WHERE UPPER(TRND_LOC_HIST.CKOUT_DATE)

             BETWEEN UPPER((SELECT TRND.LCLBT_DATE FROM TRND WHERE TRND.TRND_SERIAL = UPPER('TEST2')))

                  AND UPPER((SELECT TRND.CLBT_DATE FROM TRND WHERE TRND.TRND_SERIAL = UPPER('TEST2')))

                  AND TRND_LOC_HIST.TRND_SERIAL = UPPER('TEST2')

       

       

      FYI.......I took the TO_DATE out prior to seeing the same bad results when using UPPER in the WHERE on a DATE field.

       

      Bottom line is I don't want to use EM.........SQL Developer is far superior, but if I don't get correct results with it 100% of the time then I will have to use EM.

       

      This is what the WHERE looks like when it produces the same results in both apps

       

        WHERE TRND_LOC_HIST.CKOUT_DATE

             BETWEEN (SELECT TRND.LCLBT_DATE FROM TRND WHERE TRND.TRND_SERIAL = UPPER('TEST2'))

                  AND (SELECT TRND.CLBT_DATE FROM TRND WHERE TRND.TRND_SERIAL = UPPER('TEST2'))

                  AND TRND_LOC_HIST.TRND_SERIAL = UPPER('TEST2')