-
Query execution
I'm using the below query to retreive data from a table, the current day and the previous day and I'm doing a calculation v_calc as mentioned below.
The first loop gets the data for the current day and the inner loop gets the data for the previous day. Is there a better way to write this query? Also, if there is no record found in the inner query, I'd like to substitute the values with the current. How do I do this? Many thans for your help.
FOR x IN
(SELECT p.recid,
p.id,
p.lot_size,
p.reference,
p.shares,
pr.recid ,
pr.pricetype_id,
pr.value
FROM records p,
price pr
WHERE p.recid = pr.recid
AND p.taskid = pr.taskid
AND trunc(pr.observation_time) = v_bdate
AND p.run_id = p_taskid
)
LOOP
FOR y IN
(SELECT p.id,
p.recid ,
p.lot_size,
p.reference,
p.shares,
pr.recid ,
pr.pricetype_id,
pr.value
FROM records p,
price pr
WHERE p.recid = pr.recid
AND p.reference = x.reference
AND p.taskid = pr.taskid
AND trunc(pr.observation_time) = v_previous_business_date
AND p.taskid = v_previous_taskid
)
LOOP
v_calc := y.shares * x.lot_size * x.value -
y.shares * y.lot_size * y.value ;
dbms_output.put_line('CALC: ' || v_calc);
INSERT
INTO RECORD_HIST
(
ID,
TASKID,
VERSION,
RECID,
MEASURE
)
VALUES
(
positionmeasure_seq.nextval,
p_taskid,
1,
x.id,
v_clean_pnl
);
v_record:=v_record + 1;
END LOOP;
END LOOP;
dbms_output.put_line
(
'Records inserted: ' || v_record
)
;
--COMMIT;
thomasp
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|