IF ELSIF removal 3

ronanBas · Feb 13, 2012

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;

Dagon · Feb 13, 2012

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.

LKBrwnDBA · Feb 13, 2012

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 --

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

SantaMufasa · Feb 13, 2012

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.

ronanBas · Feb 13, 2012

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

Dagon · Feb 13, 2012

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.

ronanBas · Feb 14, 2012

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 ?

Dagon · Feb 14, 2012

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;

ronanBas · Feb 14, 2012

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

ronanBas · Feb 14, 2012

Thanks all
problem resolved

Dagon · Feb 14, 2012

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

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!

IF ELSIF removal 3

ronanBas

IS-IT--Management

Dagon

MIS

LKBrwnDBA

MIS

SantaMufasa

Technical User

ronanBas

IS-IT--Management

Dagon

MIS

ronanBas

IS-IT--Management

Dagon

MIS

ronanBas

IS-IT--Management

ronanBas

IS-IT--Management

Dagon

MIS

Similar threads

Part and Inventory Search

Sponsor