We have two Tables:
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)
WHEN NO_DATA_FOUND THEN
PROD_PRICE := NULL;
WHEN OTHERS THEN
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';
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?