PL/SQL: Null parameters from a SQL Update stops further updates

We have two Tables:
Detail
------
Detail_Ref_ID
Product_code
Price
Batch_ID
...

Transaction
-----------
Trans_Ref_ID
Process_Date
Batch_ID
...  

There can be many detail records for the Single Transaction record. The Detail_Ref_Ids will match the Trans_Ref_ID
There can be detail records that are not found in the Transaction table
The only way we know which ones are a match is to find a match on the IDs.

To Fill in the Detail.Price, we have a routine that requires the Process_date from the Transaction table.
I'm trying to use a SQL statement to update the PRICE and other fields in Detail quickly.  
The problem is that when there is no match, the required date is not passed and the entire update statement stops.



The pricing procedure looks like:

Function DoPrice(tmpProduct_code IN VARCHAR2,
		 tmpDate IN Date)

PROD_PRICE	PriceTable.Price%TYPE;

<dostuff>

EXCEPTION
         WHEN NO_DATA_FOUND THEN
           PROD_PRICE := NULL;
         RETURN PROD_PRICE;
         
         WHEN OTHERS THEN
           RETURN PROD_PRICE;

Open in new window

The SQL update looks like:
  'UPDATE Detail a
                SET a.Price = PRICING.DoPrice(a.PRODUCT_CODE (SELECT Process_Date FROM Transaction WHERE Trans_Ref_ID = Detail_Ref_ID))
                    ,<other items to set>
		    ,a.Batch_ID = (select Batch_ID from Transaction FROM Transaction WHERE Trans_Ref_ID = Detail_Ref_ID) 		
                    WHERE a.Batch_ID IS NULL';

Open in new window


What is the best way to handle this?
I would prefer to use SQL to do this rather than a procedural approach(going through each record at a time)
Should I capture the error in the Function or is there something I can do in the SQL?
GNOVAKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flow01Commented:
If you want to skip the update on details that have no coupled transaction

'UPDATE Detail a
                SET a.Price = PRICING.DoPrice(a.PRODUCT_CODE (SELECT Process_Date FROM Transaction WHERE Trans_Ref_ID = Detail_Ref_ID))
                    ,<other items to set>
                ,a.Batch_ID = (select Batch_ID from Transaction FROM Transaction WHERE Trans_Ref_ID = Detail_Ref_ID)             
                    WHERE a.Batch_ID IS NULL
                    AND (SELECT Process_Date FROM Transaction WHERE Trans_Ref_ID = Detail_Ref_ID) IS NOT NULL
0
sdstuberCommented:
something like this?
I've intentionally left off the when not matched clause.  So this is still an update-only statement

Using the merge's using clause lets us only hit the transaction table once


MERGE INTO detail a
     USING (SELECT process_date, batch_id, trans_ref_id FROM transaction) t
        ON (a.detail_ref_id = t.trans_ref_id AND a.batch_id IS NULL)
WHEN MATCHED
THEN
    UPDATE SET a.price = pricing.doprice(a.product_code, t.process_date),
    < other stuff >,
    a.batch_id = t.batch_id
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GNOVAKAuthor Commented:
Loved the merge solution - learned something new....and all this time I thought Merge was just to merge.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.