This discussion is archived
6 Replies Latest reply: Nov 26, 2012 12:18 AM by 976141 RSS

Query from Excel 2010 to Oracle DB

830762 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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. Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi Steve

    Finally, What are you doing?
  • 6. Re: Query from Excel 2010 to Oracle DB
    976141 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points