Why do you have a COMMIT in your procedure? Why is it right after you open the cursor?
myQuery VARCHAR2(4000); . . . BEGIN . . . OPEN result_cursor FOR myQuery; . . . END;
And then utilizing the same technique for your other variable.
( (T1.FROM_AMOUNT >= to_number(fromAmount) AND fromAmount is not null) or fromAmount is null )
rp0428 wrote:Not so much, since you would then need multiple cursors or dynamic SQL.
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.
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
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;
991900 wrote:Depending on your requirements, you could use the LNNVL function
Yes I've tried this only to later realize that both FROM_AMOUNT and TO_AMOUNT can have null values.