6 Replies Latest reply: Nov 26, 2012 2:18 AM by 976141 RSS

    Query from Excel 2010 to Oracle DB

    830762
      I'm having issues getting my Oracle syntax correct. I have a number of queries that run in Excel and hit an Oracle box and pull back the appropriate data. These queries worked fine for years in Excel 2003 but no longer work in Excel 2010. The issue is related to part of the WHERE clause

      (WORK_ORDER_MASTER.WOM_CMPL_DTE>? And WORK_ORDER_MASTER.WOM_CMPL_DTE<?)

      There are 2 parameters in Excel (BeginDate and EndDate). So I figured, well.... something changed with the way dates are formated or something. So I modified the query a bit.

      These work but the dates are hard coded; not what I want.

      (WORK_ORDER_MASTER.WOM_CMPL_DTE> {ts '2012-02-01 00:00:00'} And WORK_ORDER_MASTER.WOM_CMPL_DTE< {ts '2012-02-28 00:00:00'})

      (WORK_ORDER_MASTER.WOM_CMPL_DTE> to_date('2/1/2012','mm/dd/yyyy')
      And WORK_ORDER_MASTER.WOM_CMPL_DTE< to_date('2/28/2012','mm/dd/yyyy'))

      What I do want is for the Query to reference CELLS A2 and B2 and use the parameter values supplied rather than the hard coded values above. BeginDate = $A$2 and EndDate = $B$2.

      Using

      (WORK_ORDER_MASTER.WOM_CMPL_DTE>=To_Date(?,'mm/dd/yyyy') And WORK_ORDER_MASTER.WOM_CMPL_DTE<=To_Date(?,'mm/dd/yyyy'))

      produces

      [Oracle][ODBC][Ora]ORA-01843: Not a valid month

      Using

      (WORK_ORDER_MASTER.WOM_CMPL_DTE>=To_Date([BeginDate],'mm/dd/yyyy') And WORK_ORDER_MASTER.WOM_CMPL_DTE<=To_Date([EndDate],'mm/dd/yyyy'))

      produces

      [Oracle][ODBC][Ora]ORA-00936: missing expression

      I pulling my hair out trying to get the two to play nicely again. I've been monking with this one part of the WHERE cluase for hours. Any help, as always, is much appreciated!

      Thanks,

      Steve
        • 1. Re: Query from Excel 2010 to Oracle DB
          Paul  Horth
          Well, what are in $A$2 and $B$2 - give us an example of the dates in there.
          • 2. Re: Query from Excel 2010 to Oracle DB
            830762
            Sorry about that...... same as the format below

            BeginDate = '2/1/2012'
            EndDate = '2/28/2012'

            It works when I hard code it like below

            (WORK_ORDER_MASTER.WOM_CMPL_DTE> to_date('2/1/2012','mm/dd/yyyy')
            And WORK_ORDER_MASTER.WOM_CMPL_DTE< to_date('2/28/2012','mm/dd/yyyy'))

            But I can't get it to work otherwise.
            • 3. Re: Query from Excel 2010 to Oracle DB
              Sven W.
              Not excactly sure what happens, but I suspect some errors with the way how execl uses dates compared to how excel shows you the date.
              Often excel stores a day value in some epoch format.

              >
              ...
              What I do want is for the Query to reference CELLS A2 and B2 and use the parameter values supplied rather than the hard coded values above. BeginDate = $A$2 and EndDate = $B$2.

              Using
              (WORK_ORDER_MASTER.WOM_CMPL_DTE>=To_Date(?,'mm/dd/yyyy') And WORK_ORDER_MASTER.WOM_CMPL_DTE<=To_Date(?,'mm/dd/yyyy'))
              produces
              [Oracle][ODBC][Ora]ORA-01843: Not a valid month
              Test with
              WORK_ORDER_MASTER.WOM_CMPL_DTE>= ? And WORK_ORDER_MASTER.WOM_CMPL_DTE<=?
              If Excel correctly knows how to handle datatypes in this case, then this will work.
              It would also explain the error message, since the date from excel would be implicitly converted into a string, which in turn might have a different format mask than what you apply.
              Using

              (WORK_ORDER_MASTER.WOM_CMPL_DTE>=To_Date([BeginDate],'mm/dd/yyyy') And WORK_ORDER_MASTER.WOM_CMPL_DTE<=To_Date([EndDate],'mm/dd/yyyy'))

              produces

              [Oracle][ODBC][Ora]ORA-00936: missing expression
              In this case try it as a string
              WORK_ORDER_MASTER.WOM_CMPL_DTE>=To_Date('[BeginDate]','mm/dd/yyyy') And WORK_ORDER_MASTER.WOM_CMPL_DTE<=To_Date('[EndDate]','mm/dd/yyyy'))
              But I doubt that this will work. I do not see how excel will replace the value in parenthesis [] with the parameter. Not sure thou, since I do not know the excact environment where you use that sniplett.

              Edited by: Sven W. on Mar 19, 2012 7:01 PM
              • 4. Re: Query from Excel 2010 to Oracle DB
                830762
                Thanks for the response but unfortunatly I'm still getting errors.

                (WORK_ORDER_MASTER.WOM_CMPL_DTE>To_Date('[BeginDate]','mm/dd/yyyy') And WORK_ORDER_MASTER.WOM_CMPL_DTE<To_Date('[EndDate]','mm/dd/yyyy'))

                produces

                [Oracle][ODBC][Ora]ORA-01858: a non-numeric character was found where a numeric was expected

                (WORK_ORDER_MASTER.WOM_CMPL_DTE>? And WORK_ORDER_MASTER.WOM_CMPL_DTE<?)

                is what I have always used before. And it still works in Excel 2003; just not in 2010. The above code produces

                [Oracle][ODBC][Ora]ORA-01847: day of month must be between 1 and last day of month

                Still plugging away.... Man this shouldn't be as complicated as it is turning out to be.

                Thanks,

                Steve
                • 5. Re: Query from Excel 2010 to Oracle DB
                  968715
                  Hi Steve

                  Finally, What are you doing?
                  • 6. Re: Query from Excel 2010 to Oracle DB
                    976141
                    Hi

                    I had the same problem, but I solved it. I got the same error as you got. If you format BeginDate and EndDate in Excel as Text then it should work just fine.


                    Select from.......

                    (WORK_ORDER_MASTER.WOM_CMPL_DTE>To_Date('[BeginDate]','mm/dd/yyyy')
                    And WORK_ORDER_MASTER.WOM_CMPL_DTE<To_Date('[EndDate]','mm/dd/yyyy'))

                    In the excelfile* I have two cells with dates. They are formatted as Text and Align text to the right.


                    /Kent