-
I need help in deciding if the temporary table ( which I already have in db) would be a good idea to use instead of a cursor in the following eg. as it gives me time out error.
Here is just the part of the code...
If inProject > 0 then
If inRole > 0 then
/*Cursor to read each resource (Project and Role for slecetion Criteria)*/
Declare cursor resource_cursor
IS
Select Res_ID
from mwebRes
where Res_Status <> 20 AND Res_Entity_Type = 3
AND (Res_ID IN (Select AuthAttr_Res_ID
From MwebAuthAttrib
where AuthAttr_Res_ID = Res_ID
AND (AuthAttr_Work_ID IN
(Select Work_ID
From MwebWork
Where DECODE(inWorkLevel,2,Work_Par2,3,Work_Par3,4,Work_Par4,5,
Work_Par5,6,Work_par6,7,Work_par7 ,8 ,work_par8,Work_Par9 ) = inProject
AND Work_Entity_Type = 4 AND Work_Status <> 40))
AND AuthAttr_Role_ID = inRole));
Begin
/*Begin Cursor*/
Open resource_cursor;
loop
Fetch resource_cursor
into inResID;
Exit when resource_cursor%notfound ;
/*updates MwebAuthAttrib with the resources new cost or rate*/
If inCostorRate = 10 then
/*Math to determine new figure if its a percentage increase*/
If inAdjustmentType = 10 AND inDirection = 10 then
Update MwebAuthAttrib
Set AuthAttr_Amount = AuthAttr_Amount + (AuthAttr_Amount * InAmountNew)
Where AuthAttr_Res_ID = inResID
AND(AuthAttr_Work_ID IN
(Select Work_ID
From MwebWork
Where DECODE(inWorkLevel,2,Work_Par2,3,Work_Par3,4,Work_Par4,5,
Work_Par5,6,Work_par6,7,Work_par7,8,work_par8,Work_Par9)= inProject))
AND AuthAttr_Role_ID = inRole AND AuthAttr_Category = 152
AND AuthAttr_Start_Date >= dtToday;
Insert into MwebAuthAttrib(AuthAttr_id, AuthAttr_Work_ID, AuthAttr_Res_ID,
AuthAttr_Auth_ID, AuthAttr_Category, AuthAttr_Start_Date,
AuthAttr_Finish_Date, AuthAttr_Amount, AuthAttr_Role_ID)
Select MwebAuthAttrib_SEQ.NextVal,AuthAttr_Work_ID, AuthAttr_Res_ID,
AuthAttr_Auth_ID, AuthAttr_Category, dtToday, AuthAttr_Finish_Date,
AuthAttr_Amount + (AuthAttr_Amount * InAmountNew), AuthAttr_Role_ID
From MwebAuthAttrib
Where AuthAttr_Res_ID = inResID
AND(AuthAttr_Work_ID IN
(Select Work_ID
From MwebWork
Where DECODE(inWorkLevel,2,Work_Par2,3,Work_Par3,4,Work_Par4,5,
Work_Par5,6,Work_par6,7,Work_par7,8,work_par8,Work_Par9)= inProject))
AND AuthAttr_Role_ID = inRole AND AuthAttr_Category = 152
AND AuthAttr_Start_Date < dtToday AND AuthAttr_Finish_Date >= dtToday;
/*Math to determine new figure if its a percentage decrease*/
Elsif and tons of combinations...
Sonali
-
There will be overhead on using TEMPORARY TABLE. Cursor is the best option.
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
|