Home » SQL & PL/SQL » SQL & PL/SQL » add a restriction depending on IN Variable (Oracle 10gR2)
add a restriction depending on IN Variable [message #392492] Wed, 18 March 2009 03:46 Go to next message
buck444
Messages: 80
Registered: January 2006
Location: Luxembourg
Member
Is there a nice way to add a restriction, depending on an IN variable?

I call in a package a "master procedure", which itself calls the procedure where calculations are done.

Now there is a requirement to calculate only for a specific account.

Below is the code and my first idea how to solve it, but I don't find it very efficient. Is there a better way?

"Master procedure" calls calculation procedure
   PROCEDURE exec_all (
      p_begin_date       IN   DATE,
      p_end_date         IN   DATE,
   )
   IS
   BEGIN
   calc_7 (p_begin_date, p_end_date);
END exec_all


My first Idea: Add IN variable to the master procedure and depending whether Variable is NULL or not, run the normal or the recalc procedure:

   PROCEDURE exec_all (
      p_begin_date       IN   DATE,
      p_end_date         IN   DATE,
      p_recalc_account   IN   VARCHAR2
   )
   IS
   BEGIN
      IF p_recalc_account IS NULL
      THEN
         calc_7 (p_begin_date, p_end_date);
      ELSE
         calc_7 (p_begin_date, p_end_date,p_recalc_account);


calc_7:

   PROCEDURE calc_7 (p_begin_date IN DATE, p_end_date IN DATE)
   IS
   BEGIN
      MERGE INTO daily_calc dbc
         USING (SELECT *
                  FROM feesetup) tfs
         ON (    dbc.salesrep_code = tfs.salesrep_code
             AND tfs.rate <> 0
             AND UPPER (fee_type) = 'PERCENT'
             AND dbc.flag = 0
             AND dbc.tradedate >= p_begin_date
             AND dbc.tradedate <= p_end_date)
         WHEN MATCHED THEN
            UPDATE
               SET dbc.rate = tfs.rate
            ;


calc_7_recalc: Account has been added.

   PROCEDURE calc_7_recalc (p_begin_date IN DATE, p_end_date IN DATE,p_recalc_account IN VARCHAR2)
   IS
   BEGIN
      MERGE INTO daily_calc dbc
         USING (SELECT *
                  FROM feesetup) tfs
         ON (    dbc.salesrep_code = tfs.salesrep_code
             AND tfs.rate <> 0
             AND UPPER (fee_type) = 'PERCENT'
             AND dbc.flag = 0
             AND dbc.tradedate >= p_begin_date
             AND dbc.tradedate <= p_end_date
             AND [B]dbc.account=p_recalc_account[/B])
         WHEN MATCHED THEN
            UPDATE
               SET dbc.rate = tfs.rate;


The disadvantage of this solution is clearly that the package will double it's size and the maintenance afterwards will be more difficult.

thanks.
Re: add a restriction depending on IN Variable [message #392521 is a reply to message #392492] Wed, 18 March 2009 04:50 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can add have a parameter to calc_7 with a default value (say NULL) and test this default value to know if you will execute one query or the other one as you did it in exec_all procedure.

Regards
Michel
Previous Topic: How to know the column name used in query?
Next Topic: Multiple Table Insert - pls. help...
Goto Forum:
  


Current Time: Thu Dec 08 14:32:07 CST 2016

Total time taken to generate the page: 0.26233 seconds