?
Solved

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

Posted on 2014-08-29
3
Medium Priority
?
532 Views
Last Modified: 2014-08-29
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?
0
Comment
Question by:GNOVAK
3 Comments
 
LVL 21

Assisted Solution

by:flow01
flow01 earned 400 total points
ID: 40293059
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 1600 total points
ID: 40293077
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
 

Author Closing Comment

by:GNOVAK
ID: 40293533
Loved the merge solution - learned something new....and all this time I thought Merge was just to merge.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

839 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