CODEWITHSUNDEEP
sqloracle-databaseperformanceplsqlin-operator
user2488578
user2488578

Reputation: 916

PLSQL : Apply IN operator check conditionally

User inputs numbers (1,2,3 etc). My requirement is if the User input number is present in a database table then only those records should be retrieved. If User has NOT entered data then ALL the records from the table should be retrieved.

User input numbers are stored in table type and relevant code is below.

CREATE OR REPLACE TYPE ACCNT_NUMBER_TYPE AS TABLE OF NUMBER; //ACCNT_NUMBER_TYPE will have values 1,2,3 etc.

Below is my procedure.

CREATE OR REPLACE PACKAGE BODY pr_retrieve_data as
PROCEDURE FETCH_MYTABLE_DETAILS() is

FOR indx in (select column1,
                    column2
             from   SOMEOTHERTABLE SOT
             WHERE  SOT.ACCNT_NUMBER IN (SELECT * FROM TABLE(L_ACCNT_NUMBER)))
LOOP

...
-- Around 300 lines of code goes here

END LOOP;

end FETCH_MYTABLE_DETAILS;
end pr_retrieve_data;

The problem I am facing is, I am not able to write logic for the case where User has not input data. In this case I am supposed to retrieve all the records. But, as per the above FOR loop if the User has not input data then it will NOT fetch any records.

I could write another FOR loop by putting condition if User data is null. But, there will be repetition of 300 lines of code. This will increate maintenace.

How can I tweek the FOR loop such that if User enters data then only that record is retrieved and when User does not enter data then ALL the records are retrieved?

Upvotes: 0

Views: 58

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Looks like variable is initialized and filled with nulls. Check if there are any non nulls in l_accnt_number and construct loop condition like here:

... 
procedure fetch_mytable_details() is
  l_accnt_not_null number(6);
begin
  select count(1) 
    into l_accnt_not_null 
    from table(l_accnt_number) 
    where column_value is not null;

  for indx in (select  column1, column2
                 from  someothertable sot
                 where l_accnt_not_null = 0 
                    or sot.accnt_number in (select * from table(l_accnt_number)))
...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you want not exists:

select column1, column2
from SOMEOTHERTABLE SOT
where SOT.ACCNT_NUMBER in (select * from TABLE(L_ACCNT_NUMBER)) or
      not exists (select 1 from TABLE(L_ACCNT_NUMBER));

This will return all rows in the outer query if the table of inputs has no rows.

Upvotes: 2

Related Questions