This discussion is archived
9 Replies Latest reply: Apr 25, 2013 10:10 AM by Frank Kulash RSS

Conditionally apply AND condition

994903 Newbie
Currently Being Moderated
CREATE OR REPLACE PROCEDURE SP_TFS_REPORT (LoginId IN STRING,
status IN STRING,
fromAmount IN STRING DEFAULT NULL,
toAmount IN STRING DEFAULT NULL,
accNumber IN STRING,
result_cursor OUT SYS_REFCURSOR)
IS
.....
BEGIN
.....
OPEN result_cursor FOR SELECT * FROM TEST_TABLE T1
WHERE T1.LOGIN_ID = LoginId AND
T1.STATUS = status AND
T1.FROM_AMOUNT >= to_number(fromAmount) AND --This needs to be applied only if fromAmount paramter is not null*
T1.TO_AMOUNT <= to_number(toAmount) AND --This needs to be applied only if fromAmount paramter is not null*
T1.ACCOUNTNUMBER = accNumber;

COMMIT;

As you can see 'fromAmount' and 'toAmount' are optional paramters to the stored procedure. So the AND conditions corresponding to them need to be conditionally applied. In other words they need to be applied only when I get a NOT NULL value.
I know we can do this using IF ELSE condition but is there any efficient way to do this?

My DB details are:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

Thanks in advance for any help!!
  • 1. Re: Conditionally apply AND condition
    rp0428 Guru
    Currently Being Moderated
    >
    I know we can do this using IF ELSE condition but is there any efficient way to do this?
    >
    That IS an efficient way to do that.

    Construct the query into a string and then open the cursor for the string.
    myQuery VARCHAR2(4000);
    . . .
    BEGIN
        . . .
        OPEN result_cursor FOR myQuery;
        . . .
    END;
    Why do you have a COMMIT in your procedure? Why is it right after you open the cursor?
  • 2. Re: Conditionally apply AND condition
    Tubby Guru
    Currently Being Moderated
    (
    (T1.FROM_AMOUNT >= to_number(fromAmount) AND fromAmount is not null)
    or fromAmount is null
    )
    And then utilizing the same technique for your other variable.

    Cheers,

    Edited by: Tubby on Apr 25, 2013 8:53 AM

    I see I misread the question a little bit, I thought both parameters were optional and would require the same technique. As usual, please proceed with Frank's suggestion below ... he possesses much better critical reading skills than I do :)
  • 3. Re: Conditionally apply AND condition
    Tubby Guru
    Currently Being Moderated
    rp0428 wrote:
    >
    I know we can do this using IF ELSE condition but is there any efficient way to do this?
    >
    That IS an efficient way to do that.
    Not so much, since you would then need multiple cursors or dynamic SQL.

    The option I outlined in my previous post would be more efficient (from the perspective of parsing and cursor management) so long as it doesn't cause the optimizer to produce a "whacky" plan. Though given the mandatory parameters to the routine I'm guessing that's not too much of a concern here.

    Cheers,
  • 4. Re: Conditionally apply AND condition
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    You can use nested compound conditions, like this:
    WHERE   T1.LOGIN_ID = LoginId 
    AND     T1.STATUS = status 
    AND     (    (     T1.FROM_AMOUNT     >= to_number (fromAmount) 
              AND   T1.TO_AMOUNT      <= to_number (toAmount)
              )
         OR   fromAmount             is null
         )
    AND     T1.ACCOUNTNUMBER = accNumber
    \
    Since you're doing this in PL/SQL, you might start by testing if fromAmount is NULL, and then executing either of 2 separate cursors, depending on the answer. (Maybe that's what you meant by using IF ... THEN ... ELSE.) This will make maintenance more difficult, but it could improve performance.
  • 5. Re: Conditionally apply AND condition
    994903 Newbie
    Currently Being Moderated
    Yes both parameters are optional. It's my typo in the original posting.
    Your solution works. But can this be done using Case statement? I was doing something like this but seems it is not working.

    WHERE T1.LOGIN_ID = LoginId AND
    T1.STATUS = status AND
    CASE WHEN fromAmount IS NOT NULL AND (T1.FROM_AMOUNT >= to_number(fromAmount), T1.FROM_AMOUNT)) THEN 1
    ELSE 0
    END=1
    AND
    CASE WHEN toAmount IS NOT NULL AND (T1.TO_AMOUNT <= to_number(toAmount), T1.TO_AMOUNT)) THEN 1
    ELSE 0
    END=1
    AND
    T1.ACCOUNTNUMBER = accNumber;

    Edited by: 991900 on Apr 25, 2013 9:40 AM
  • 6. Re: Conditionally apply AND condition
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Sure, you can use CASE in a WHERE clause, but there's usually no benefit in doing so.
    Why not
    WHERE   T1.LOGIN_ID     = LoginId 
    AND     T1.STATUS      = status 
    AND     T1.FROMAMOUNT      >= nvl ( to_number (l_fromAmount1)
                               , T1.FROMAMOUNT
                          )
    AND     T1.FROMAMOUNT   <= nvl ( to_number (l_fromAmount2)
                                , T1.FROMAMOUNT
                          )
    AND     T1.ACCOUNTNUMBER = accNumber; 
    ?
    This assumes t1.fromamount is not NULL.

    It looks like you've changed your requirements: earlier, you were interested in t1.from_amount and t1.to_amount, and now only t1.fromamount.
  • 7. Re: Conditionally apply AND condition
    994903 Newbie
    Currently Being Moderated
    Yes I've tried this only to later realize that both FROM_AMOUNT and TO_AMOUNT can have null values.
  • 8. Re: Conditionally apply AND condition
    994903 Newbie
    Currently Being Moderated
    Sorry I was playing around and copied the same version. I corrected it.
  • 9. Re: Conditionally apply AND condition
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    991900 wrote:
    Yes I've tried this only to later realize that both FROM_AMOUNT and TO_AMOUNT can have null values.
    Depending on your requirements, you could use the LNNVL function

    If you get stuck, post your best attempt, along with a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, a few sets of parametes, and the results you want from each set given the same sample data.
    Explain, using specific examples, how you get those results from that data.
    See the forum FAQ {message:id=9360002}

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points