Solved

PL/SQL: Fast insert based on 2nd table

Posted on 2013-11-20
2
315 Views
Last Modified: 2013-11-25
I have three tables:
TransactionTable contains product_id, DateSold - has a couple million records
ExceptionTable contains product_id and E_Code - empty to start, will be populated based on:
ProductTable contains product_id, myCondition, LastChangeDate

ProductTable is NOT unique for product_id but is unique with product_id & LastChangeDate
TransactionTable can have multiple records for product_id on the same DateSold

I need to go through the TransactionTable, get the product_id, look into the ProductTable. If the Product_ID exists in the ProdcutTable
where the ProductTable Mycondition ='X'
I have to get the one record from the ProductTable where the maximum LastChangeDate is less than the DateSold and use this for the above comparison

The way I'm doing it now is sending each TransactionTable.product_id and the DateSold to the following and then updating the ExceptionTable where this returns 'X':

SELECT myCondition INTO tmpReturn
        FROM
            (
             SELECT myCondition
             FROM ProductTable
             WHERE ProductTable.product_id = <Passed product_id>
                 AND ProductTable.LastChangeDate = (SELECT MAX(LastChangeDate)
                                         FROM  ProductTable
                                         WHERE ProductTable.product_id = <Passed product_id>
                                             AND ProductTable.LastChangeDate <= (<Passed DateSold>))
                 AND ROWNUM = 1
            );

        CASE WHEN tmpReturn = 'X'
             THEN tmpReturn := 'Y';
             ELSE tmpReturn := 'N';
        END CASE;

       RETURN tmpReturn;

Is there a better/faster way to do this?
0
Comment
Question by:GNOVAK
2 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39662993
Try row_number.

Something like:
select myCondition from (
SELECT myCondition, row_number() over(partition by product_id order by lastchangedate desc) myrn FROM ProductTable where...
)
where myrn=1
0
 
LVL 32

Expert Comment

by:awking00
ID: 39663077
Can you provide some sample data and your expected output?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now