Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

IF ELSIF removal 3

Status
Not open for further replies.
ronanBas

ronanBas

IS-IT--Management
Nov 30, 2011
15
0
0
IN
In my procedure I have used IF ELSIF often . The part of the procedure is as attached .
is there any other way to replace the IF ELSIF in above case and improve performance or may be remove the cursor loop completely ?
Thanks

create or replace
FUNCTION fn_tenor_bucket
(
pi_template_desc VARCHAR2,
pi_as_of_date DATE,
pi_cashflow_date DATE)
RETURN DATE
IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_cashflow_date DATE;
v_start_dt DATE;
v_end_dt DATE;
v_start_dt1 DATE;
v_bucket glrs_agg.TBL_AGG_GT_TMP_TENOR_BUCKETS%ROWTYPE;
v_bucket_type VARCHAR2(20);
v_counter NUMBER :=0 ;
v_tenor_bucket VARCHAR2(10);
isBusinessDay VARCHAR2(20);
v_count NUMBER :=0;
v_inc_date DATE;
CURSOR c_get_tenor_buckets
IS
SELECT b.tenor_bucket_name ,
c.code ,
b.tenor_bucket_value ,
b.user_tenor_buckets_id
FROM glrs.tbl_glrs_user_tenor_template a ,
glrs.tbl_glrs_user_tenor_buckets b ,
glrs.tbl_glrs_tenor_bucket_type c
WHERE a.user_tenor_template_id = b.user_tenor_template_id
AND a.tenor_template_name = pi_template_desc
AND b.tenor_bucket_type_id = c.tenor_bucket_type_id
ORDER BY b.user_tenor_buckets_id;
BEGIN
DELETE FROM tbl_agg_gt_tmp_tenor_buckets;
commit;
v_start_dt:= pi_as_of_date + 1;
FOR i IN c_get_tenor_buckets
LOOP
IF i.code = 'D' THEN
v_inc_date:= v_start_dt;
WHILE (GLRS.fnc_is_business_date ('US', to_char(v_inc_date,'yyyy-mm-dd'))) = 'N' LOOP
v_count:=v_count+1;
v_inc_date:=v_inc_date+1;
END LOOP;
v_end_dt:= v_start_dt + v_count;
v_count:=0;
ELSIF i.code = 'EOW' THEN
v_end_dt := (pi_as_of_date) + (7 * i.tenor_bucket_value);
ELSIF i.code = 'EOM' THEN
v_end_dt := v_start_dt+(15*i.tenor_bucket_value);

ELSIF i.code = 'M' and i.tenor_bucket_name in ('31 - 60 Days','61 - 90 Days') THEN
v_end_dt := (v_start_dt-1)+(30*i.tenor_bucket_value);
ELSIF i.code = 'M' and i.tenor_bucket_name NOT IN ('31 - 60 Days','61 - 90 Days') THEN
--v_start_dt:=add_months(pi_as_of_date,(3+v_count))+1;
v_start_dt1:=add_months(pi_as_of_date,(3+v_count))+1;
v_end_dt := Add_Months(v_start_dt1 - 1,i.tenor_bucket_value);
v_count:=v_count+i.tenor_bucket_value;
ELSIF i.code = 'MY' THEN
v_end_dt := add_months(pi_as_of_date,12);
ELSIF i.code = 'Y' THEN
v_end_dt := Add_Months(v_start_dt - 1,i.tenor_bucket_value * 12);
ELSIF i.code = 'ALR' THEN
v_end_dt := Add_Months(v_start_dt,100 * 12);
END IF;
INSERT
INTO tbl_agg_gt_tmp_tenor_buckets
(
st_dt ,
end_dt ,
tenor_bucket ,
bucket_type ,
bucket_order ,
bucket_value
)
VALUES
(
v_start_dt ,
v_end_dt ,
i.tenor_bucket_name ,
i.code ,
i.user_tenor_buckets_id ,
i.tenor_bucket_value
);
COMMIT;
v_start_dt := v_end_dt + 1;
END LOOP;

select end_dt into v_cashflow_date from tbl_agg_gt_tmp_tenor_buckets where st_dt <=pi_cashflow_date and end_dt >=pi_cashflow_date;

RETURN v_cashflow_date;
END fn_tenor_bucket;
 
Sort by date Sort by votes
You might be able to do the insert to tbl_agg_gt_tmp_tenor_buckets as a block insert using CASE statements e.g.

Code: 
INSERT INTO tbl_agg_gt_tmp_tenor_buckets
(
st_dt ,
end_dt ,
tenor_bucket ,
bucket_type ,
bucket_order ,
bucket_value
)
SELECT
pi_as_of_date + 1
CASE when c.code = 'D' THEN ...
          c.code = 'EOW' THEN ...

END,
i.tenor_bucket_name ,
i.code ,
i.user_tenor_buckets_id ,
i.tenor_bucket_value
WHERE a.user_tenor_template_id = b.user_tenor_template_id
AND a.tenor_template_name = pi_template_desc
AND b.tenor_bucket_type_id = c.tenor_bucket_type_id
ORDER BY b.user_tenor_buckets_id

You'd have to do some shuffling of the code e.g. the "WHILE (GLRS.fnc_is_business_date " loop would probably have to be moved into a separate function which you could call in the SQL above. Most of the others look fairly straightforward assignments, so I don't see anything that is impossible to do with this technique.

 
Upvote 0 Downvote


How many rows are returned by the CURSOR c_get_tenor_buckets?

Because they are type NUMBER and DATE, they occupy very little memory, therefore you could define corresponding arrays and BULK collect all cursor values at once, kinda like:
Code: 
-- Etc --
OPEN c_get_tenor_buckets;
FETCH c_get_tenor_buckets
  BULK COLLECT INTO
       v_tenor_bucket_name,
       v_code,
       v_tenor_bucket_value,
       v_user_tenor_buckets_id;       

FOR i IN 1..v_tenor_bucket_name.COUNT
LOOP
  -- 
  -- Do your IF's HERE ---
  --
END LOOP;
FORALL i IN 1..v_tenor_bucket_name.COUNT 
  INSERT INTO tbl_agg_gt_tmp_tenor_buckets
       ( st_dt
       , end_dt
       , tenor_bucket
       , bucket_type
       , bucket_order
       , bucket_value 
       )
  VALUES 
       ( v_start_dt(i)
       , v_end_dt(i)
       , v_tenor_bucket_name(i)
       , v_code(i)
       , user_tenor_buckets_id(i)
       , tenor_bucket_value(i) 
       );
COMMIT;
-- Etc --
[3eyes]


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Upvote 0 Downvote
And one additional comment about LKBrwnDBA's code...It is so easy to follow because he has formatted his code so readably. That is a suggestion, Ronan, that I offer regarding your code...format/indent your code so that it follows this general structure:
Code: 
DECLARE
    VAR1....
    VAR2....
    PROCEDURE
        BEGIN
            IF <CONDITION> THEN
                <CODE>
                <CODE>
                IF <CONDITION> THEN
                    <CODE>
                ELSE
                    <CODE>
                END IF;
            ELSIF <CONDITION> THEN
                <CODE>
            ELSIF <CONDITION> THEN
                <CODE>
                <CODE>
            END IF;
        END;
BEGIN
    <CODE>
    FOR X IN (<SELECT STATEMENT>) LOOP;
        <CODE>
        IF <CONDITION>  THEN
            <CODE>
        ELSE
            <CODE>
        END IF;
        <CODE>
    END LOOP;
    <CODE>
END;
/
Without formatting of your code, above, it complicates our reading of the code enormously.


Thanks,

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.
 
Upvote 0 Downvote
Thanks all for your comments
I have modified the procedure and the variables are as below
Code: 
 v_tenor_bucket_name         glrs.tbl_glrs_user_tenor_buckets.tenor_bucket_name%TYPE;
    v_code                      glrs.tbl_glrs_tenor_bucket_type.code%TYPE;
    v_tenor_bucket_value        glrs.tbl_glrs_user_tenor_buckets.tenor_bucket_value%TYPE;
    v_user_tenor_buckets_id     glrs.tbl_glrs_user_tenor_buckets.user_tenor_buckets_id%TYPE;
When I have the following code

Code: 
  OPEN c_get_tenor_buckets;
   LOOP

  FETCH c_get_tenor_buckets BULK COLLECT INTO 
        v_tenor_bucket_name,
        v_code,
        v_tenor_bucket_value,
        v_user_tenor_buckets_id;                         
   
  FOR i in 1..v_tenor_bucket_name.COUNT


    LOOP
          -- ALL IF Code 
      END LOOP;

   -- and then the next condition   

    FORALL i in 1..v_tenor_bucket_name.COUNT

However Im getting the error

Error: PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
Line: 45
Text: v_tenor_bucket_name,

Error: PLS-00487: Invalid reference to variable 'V_TENOR_BUCKET_NAME'
Line: 50
Text: FOR i in 1..v_tenor_bucket_name.COUNT

Why would I get above errors ?

Thanks
 
Upvote 0 Downvote
Without seeing all the code, it's difficult to see what the problem is. But it sounds as if some of the variables you are fetching into are declared as arrays and some as scalars. You will need to use arrays for all of them.

 
Upvote 0 Downvote
Code: 
CREATE OR REPLACE FUNCTION fn_tenor_bucket_poc
    (
      pi_template_desc VARCHAR2,
      pi_as_of_date DATE,
      pi_cashflow_date DATE)
    RETURN DATE
  IS
   PRAGMA                AUTONOMOUS_TRANSACTION;

    v_cashflow_date             DATE;
    v_start_dt                  DATE;
    v_end_dt                    DATE;
    v_start_dt1                 DATE;
    v_count                     NUMBER :=0;
    v_inc_date                  DATE;
    v_tenor_bucket_name         glrs.tbl_glrs_user_tenor_buckets.tenor_bucket_name%TYPE;
    v_code                      glrs.tbl_glrs_tenor_bucket_type.code%TYPE;
    v_tenor_bucket_value        glrs.tbl_glrs_user_tenor_buckets.tenor_bucket_value%TYPE;
    v_user_tenor_buckets_id     glrs.tbl_glrs_user_tenor_buckets.user_tenor_buckets_id%TYPE;
    

    CURSOR c_get_tenor_buckets
    IS
       SELECT b.tenor_bucket_name ,
        c.code                    ,
        b.tenor_bucket_value      ,
        b.user_tenor_buckets_id
         FROM glrs.tbl_glrs_user_tenor_template a ,
        glrs.tbl_glrs_user_tenor_buckets b        ,
        glrs.tbl_glrs_tenor_bucket_type c
        WHERE a.user_tenor_template_id = b.user_tenor_template_id
      AND a.tenor_template_name        = pi_template_desc
      AND b.tenor_bucket_type_id       = c.tenor_bucket_type_id
     ORDER BY b.user_tenor_buckets_id;

  BEGIN
 --   DELETE FROM tbl_agg_gt_tmp_tenor_buckets;
    commit;
    v_start_dt:= pi_as_of_date + 1;
  
  OPEN c_get_tenor_buckets;
   LOOP

  FETCH c_get_tenor_buckets BULK COLLECT INTO 
        v_tenor_bucket_name,

        v_code,
        v_tenor_bucket_value,
        v_user_tenor_buckets_id;                         
   
  FOR i in 1..v_tenor_bucket_name.COUNT

    LOOP
          IF i.code = 'D' THEN
              v_inc_date:= v_start_dt;
          WHILE (GLRS.fnc_is_business_date ('US', to_char(v_inc_date,'yyyy-mm-dd'))) = 'N'
            LOOP
              v_count:=v_count+1;
              v_inc_date:=v_inc_date+1;
             END LOOP;
              v_end_dt:= v_start_dt + v_count;
              v_count:=0;
          ELSIF i.code = 'EOW' THEN
              v_end_dt := (pi_as_of_date) + (7 * i.tenor_bucket_value);
          ELSIF i.code = 'EOM' THEN
              v_end_dt := v_start_dt+(15*i.tenor_bucket_value);

          ELSIF i.code = 'M' AND i.tenor_bucket_name IN ('31 - 60 Days','61 - 90 Days') THEN
              v_end_dt := (v_start_dt-1)+(30*i.tenor_bucket_value);
          ELSIF i.code = 'M' and i.tenor_bucket_name NOT IN ('31 - 60 Days','61 - 90 Days') THEN
                v_start_dt1:=add_months(pi_as_of_date,(3+v_count))+1;
               v_end_dt := Add_Months(v_start_dt1 - 1,i.tenor_bucket_value);
               v_count:=v_count+i.tenor_bucket_value;
          ELSIF i.code = 'MY' THEN
              v_end_dt :=  add_months(pi_as_of_date,12);
          ELSIF i.code = 'Y' THEN
              v_end_dt := Add_Months(v_start_dt - 1,i.tenor_bucket_value * 12);
          ELSIF i.code = 'ALR' THEN
              v_end_dt := Add_Months(v_start_dt,100 * 12);
          END IF;

      END LOOP;

     

    FORALL i in 1..v_tenor_bucket_name.COUNT

     INSERT INTO tbl_agg_gt_tmp_tenor_buckets
       ( st_dt
       , end_dt
       , tenor_bucket
       , bucket_type
       , bucket_order
       , bucket_value
       )
     VALUES
       ( v_start_dt(i)
       , v_end_dt(i)
       , v_tenor_bucket_name(i)
       , v_code(i)
       , user_tenor_buckets_id(i)
       , tenor_bucket_value(i)
       );
     COMMIT;

     v_start_dt := v_end_dt + 1;

    END LOOP;

    SELECT end_dt
      INTO v_cashflow_date
      FROM tbl_agg_gt_tmp_tenor_buckets
     WHERE st_dt <= pi_cashflow_date
       AND end_dt >= pi_cashflow_date;

  RETURN v_cashflow_date;
  END fn_tenor_bucket_poc;

However Im getting the error
Error: PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
Line: 45
Text: v_tenor_bucket_name,

Error: PLS-00487: Invalid reference to variable 'V_TENOR_BUCKET_NAME'
Line: 50
Text: FOR i in 1..v_tenor_bucket_name.COUNT

Why would I get above errors ?

 
Upvote 0 Downvote
You've declared all your types as scalars, but they need to be arrays e.g.

Code: 
type t_tenor_bucket_name is table of glrs.tbl_glrs_user_tenor_buckets.tenor_bucket_name%type index by pls_integer;

v_tenor_bucket_name t_tenor_bucket_name;

 
Upvote 0 Downvote
Thanks
I have made the changes now
Code: 
 FOR i in 1..v_tenor_bucket_name.COUNT

    LOOP
          IF i.code = 'D' THEN
              v_inc_date:= v_start_dt;

Error: PLS-00487: Invalid reference to variable 'I'
Line: 63
Text: IF i.code = 'D' THEN

 
Upvote 0 Downvote
Yes, you'd have to use v_code, which I assume is what you did.

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

kernal
  • Locked
  • Question
If there is a Fee on Class Level then that fee overrides Fee on Course Level 1
Replies
3
Views
56
kernal
kernal
kernal
  • Locked
  • Question
If Day of the Week='Y' (i.e. Mon='Y') and between a Start Date and End Date then Count #of Days 1
Replies
1
Views
60
Beilstwh
Beilstwh
kernal
  • Locked
  • Question
CONCAT DayofWeek Fields if Field Value =Y 3
Replies
4
Views
66
kernal
kernal
kernal
  • Locked
  • Question
Retrieve records if they meet a certain criteria (1 record is &lt;1000 and the other one &gt;=1000) 2
Replies
3
Views
55
carp
carp
kernal
  • Locked
  • Question
Count ID in a term if they don't exist in a previous term 2
Replies
3
Views
48
kernal
kernal

Part and Inventory Search

Sponsor

Back
Top