none
How to convert an Access query to an Oracle query? RRS feed

  • Question

  • Hello,

    I have a query from MS Access.  It queries an Oracle database.
    When I copy and paste the query into SQL Developer with an Oracle connection, it does not work.

    The query has the followings that Oracle does not like
    DateDiff
    Now()
    IIF

    I replaced the above 3 with the following respectively
    date2 - date1
    SYSDATE
    CASE

    The query now runs in the Oracle connection, but it returns more rows than the corresponding query in Access.

    What do you recommend me to do?

    Thanks


    Friday, April 15, 2016 10:43 PM

Answers

  • Hi Carbond,

    Here you want a same result from an oracle query that you are getting from an Access.

    You also made some changes in functions that you used in queries.

    But I think that there is still some difference that you are missing.

    I mean to say that you change the appropriate functions but these functions are not giving you an output same as Access. There is still something that make difference and because of this you getting an extra record from oracle.

    So as other community members had suggested to you, make your query simple and try to execute.

    Check the values returned by the function that you used and compare it with the result of Access and again make appropriate changes in it to get same results like Access.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 18, 2016 12:49 AM
    Moderator
  • Oracle or any other database engine does not know about VBA functions. That's no big surprise.

    I would debug this by first comparing the two result sets and deciding which one is correct. Let's say you determine Access is correct.

    Then look at the extra bad records. What do they have in common? Maybe they all have one extra day worth of data. You would then suspect the date math does not quite work like you thought it would, and you would ask an Oracle DBA for assistance, or post in an Oracle forum, or read the Help pages some more.

    Good luck!


    -Tom. Microsoft Access MVP

    Saturday, April 16, 2016 1:45 AM
  • The Oracle Date data type is similar to the Access DateTime data type in that both use an internal representation of the full date and time, whereas the ISO/IEC 9075 standard defines separate DATE and TIME date types, along with a combined TIMESTAMP data type.  Simple date arithmetic can therefore result in a value which includes a fractional element.  The VBA DateDiff function on the other hand returns an integer of the number of complete intervals specified by the first argument passed into the function, so if in your original query this is "d" the result would differ from a simple subtraction of Date1 - Date2 if either of the values contain a non zero time of day.

    I'd suggest that you temporarily change the Access query so that it uses a simple subtraction rather than calling the DateDiff function.  If this then returns the same rows as the Oracle query it would confirm that this is the cause of the discrepancy.

    If you do confirm that this is the cause of the discrepancy I'm afraid that I'm insufficiently familiar with Oracle's flavour of SQL to know how the DateDiff function can be replicated, though I have no doubt in can be.

    Ken Sheridan, Stafford, England

    Saturday, April 16, 2016 11:47 AM

All replies

  • Oracle or any other database engine does not know about VBA functions. That's no big surprise.

    I would debug this by first comparing the two result sets and deciding which one is correct. Let's say you determine Access is correct.

    Then look at the extra bad records. What do they have in common? Maybe they all have one extra day worth of data. You would then suspect the date math does not quite work like you thought it would, and you would ask an Oracle DBA for assistance, or post in an Oracle forum, or read the Help pages some more.

    Good luck!


    -Tom. Microsoft Access MVP

    Saturday, April 16, 2016 1:45 AM
  • The Oracle Date data type is similar to the Access DateTime data type in that both use an internal representation of the full date and time, whereas the ISO/IEC 9075 standard defines separate DATE and TIME date types, along with a combined TIMESTAMP data type.  Simple date arithmetic can therefore result in a value which includes a fractional element.  The VBA DateDiff function on the other hand returns an integer of the number of complete intervals specified by the first argument passed into the function, so if in your original query this is "d" the result would differ from a simple subtraction of Date1 - Date2 if either of the values contain a non zero time of day.

    I'd suggest that you temporarily change the Access query so that it uses a simple subtraction rather than calling the DateDiff function.  If this then returns the same rows as the Oracle query it would confirm that this is the cause of the discrepancy.

    If you do confirm that this is the cause of the discrepancy I'm afraid that I'm insufficiently familiar with Oracle's flavour of SQL to know how the DateDiff function can be replicated, though I have no doubt in can be.

    Ken Sheridan, Stafford, England

    Saturday, April 16, 2016 11:47 AM
  • Hi Carbond,

    Here you want a same result from an oracle query that you are getting from an Access.

    You also made some changes in functions that you used in queries.

    But I think that there is still some difference that you are missing.

    I mean to say that you change the appropriate functions but these functions are not giving you an output same as Access. There is still something that make difference and because of this you getting an extra record from oracle.

    So as other community members had suggested to you, make your query simple and try to execute.

    Check the values returned by the function that you used and compare it with the result of Access and again make appropriate changes in it to get same results like Access.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 18, 2016 12:49 AM
    Moderator