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

Question

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.

try operator IN instead of =

Zohar Peled
– Zohar Peled

2015-03-31 10:20:21 +00:00
Commented Mar 31, 2015 at 10:20 — Zohar Peled
– Zohar Peled, Commented Mar 31, 2015 at 10:20
i have tried in also .its not working for parameter 'ALL'

user2862073
– user2862073

2015-03-31 10:21:04 +00:00
Commented Mar 31, 2015 at 10:21 — user2862073
– user2862073, Commented Mar 31, 2015 at 10:21

Ben · Accepted Answer · 2015-03-31 10:20:50Z

1

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

answered Mar 31, 2015 at 10:20

Ben

53.1k36 gold badges133 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben Over a year ago

From what you've got in your question this will work fine @user2862073; have you tried it and if it doesn't work what exactly does your data look like?

thots · Accepted Answer · 2015-03-31 10:41:27Z

0

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;

answered Mar 31, 2015 at 10:41

thots

1105 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related