CODEWITHSUNDEEP
sqloracle-database
user2862073
user2862073

Reputation: 254

how to pass multiple values(comma separated) single variable in oracle sql

i need to pick the count based on given parameter value in a function.function parameter value can be 'I' or 'D' or 'ALL'. 'ALL' means I and D both.

for ex:

  create or replace function test1( FLAG in varchar2) return varchar2

    as 

    b varchar2(20);
    c varchar2(20);

    begin
    if flag='ALL'
    then 
    c:='I','D';
    else 
    c:=FLAG;
    end if;
    select count(*) into b from test where id=c;

    return b;

    end;

if i pass I or D its working fine.I want to pass 'ALL' as in parameter to pick all the count for both (I,D) but I am facing error.

Let me know if any other info required at my end.

Upvotes: 0

Views: 2039

Answers (2)

thots
thots

Reputation: 110

If else block needs to be changed to meet your requirements. Updated code is listed below.

create or replace function test1( flag_var in varchar2) return varchar2

as 

count_num NUMBER(20);

begin

  if flag_var='ALL'
   then 
      select count(*) into count_num from TEST where ID in ('I' , 'D');
  else 
     select count(*) into count_num from TEST where ID = flag_var;
  end if;

return count_num;

end;

Upvotes: 0

Ben
Ben

Reputation: 52863

There are a few ways of doing this, all over complicated for your scenario. It'd be easier if you used the logic from your IF statement in your SQL:

select count(*) into b from test where flag = 'ALL' or id = flag

Thus, if you pass in the FLAG ALL then you get everything in the table, otherwise if FLAG is not ALL then you restrict it to the specific value. If you want to restrict the ID to only the 2 values mentioned then you could do this:

select count(*) into b 
  from test 
 where ( flag = 'ALL' and id in ('I','D') )
    or id = flag

Upvotes: 1

Related Questions