Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    2

    Unanswered: Where Clause Subquery with Dual-Keyed table

    Hello, and thanks in advance for your help. I've googled this for an hour now and have been unable to find the information I need, and this forum seemed to have a friendly community and decent answers.

    The key elements of my situation are:
    * I have a web-page that displays ship-history data
    * That web-page is running off of an Access DB
    * That Access DB contains a copy of our Ship History data
    * That Ship History data resides in an ORACLE database not under my control

    Now that I have a copy of the data, I need to keep it up-to-date. I am trying to refresh the data on a regular basis, but it takes about 2 hours to run the queries that refresh. Because the ORACLE database is slow, I don't want to clear and repopulate the entire Access Table each night, instead, I want to just add the rows that are in the Data Warehouse but not in my Access Table.

    I have a Pass-Through-Query (ptqShipHistory) in my Access database that has the ORACLE data, and a table in my Access DB that receives the data (tblShipHistory).

    If I want to clear ALL the data, and repopulate ALL the data, I can just do this:

    DELETE * FROM tblShipHistory;
    INSERT INTO tblShipHistory SELECT * from ptqShip History;

    But I want to just INSERT the rows that are "new" in the ptqShipHistory table, and leave the existing data alone. Which leads me to a querie more like this:

    INSERT INTO tblShipHistory SELECT * from ptqShipHistory where ptqShipHistory ID in (
    select ID
    from
    ptqShipHistory as newData
    LEFT JOIN
    tblShipHistory as oldData
    ON
    newData.ID = oldData.ID
    where
    oldData.ID is not null
    );

    OK, here's where it gets hairy. I simplified that. This Data Warehouse has a dual primary key. ID is not one field, it's two fields: ORDER_NUM, ORDER_LINE.

    I'm OK on the subquery, it becomes

    select newData.ORDER_NUM, newData.ORDER_LINE
    from
    ptqShipHistory as newData
    LEFT JOIN
    tblShipHistory as oldData
    ON
    newData.ORDER_NUM = oldData.ORDER_NUM
    AND
    newData.ORDER_LINE = oldData.ORDER_LINE
    where
    oldData.ORDER_LINE is not null

    But when I try to try to make the WHOLE query, I get stick at the part in curly braces:

    INSERT INTO tblShipHistory SELECT * from ptqShipHistory where ptqShipHistory {WHAT DO I PUT HERE} IN (
    select newData.ORDER_NUM, newData.ORDER_LINE
    from
    ptqShipHistory as newData
    LEFT JOIN
    tblShipHistory as oldData
    ON
    newData.ORDER_NUM = oldData.ORDER_NUM
    AND
    newData.ORDER_LINE = oldData.ORDER_LINE
    where
    oldData.ORDER_LINE is not null
    )

    I tried putting (ORDER_NUM, ORDER_LINE) in the curly-braces spot. The error message led me to try an EXISTS query, but that failed (I got all records or none, never just the new records). Although that might be because I don't get EXISTS, so maybe a good EXISTS query would work.


    I also tried concatenating the two fields into one in both the main and subqueries. Slow-as-molasses.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Is the primary key of the local table also composed of two fields (ORDER_NUM + ORDER_LINE)? If not what's its exact definition?

    2. Whatever the answers to the first question will be, the needed query will probably be slow.

    3. When you need to refresh the data, can't you create a linked table to the Oracle database, then use an INSERT query (or a SELECT... INTO query) to populate the local table from the linked table, then finally drop the linked table?
    Have a nice day!

  3. #3
    Join Date
    May 2012
    Posts
    2

    Response to Sinndho

    Quote Originally Posted by Sinndho View Post
    1. Is the primary key of the local table also composed of two fields (ORDER_NUM + ORDER_LINE)? If not what's its exact definition?
    Yes, although I could create an autonumber ID on the access side if that would help the speed. Obviously, it won't help with the join, which must be on the fields they both share.

    Quote Originally Posted by Sinndho View Post
    2. Whatever the answers to the first question will be, the needed query will probably be slow.
    You may very well be right, although this dual key is indexed in the ORACLE DB and in my ACCESS DB, which makes it fast enough to be measured in minutes rather than hours (when I run the subquery, at-least).

    Quote Originally Posted by Sinndho View Post
    3. When you need to refresh the data, can't you create a linked table to the Oracle database, then use an INSERT query (or a SELECT... INTO query) to populate the local table from the linked table, then finally drop the linked table?
    Hmmm. Maybe I could make the subquery a separate query object and then insert the contents of that object into the access table. Thanks, I will give that a try. That makes it harder for my implementation (since I'll have to make such queries in my access DB instead of sending pure SQL across my ODBC connection), but it might work.

    Thanks for taking the time to respond. Definitely some steps forward in what you have written.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •