case statement in oracle

I want to choose 'A' meaning All
I have values E- East
                      W- West
                       A- All
Need a case statement
table is department and column name is division cd
LVL 6
anumosesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
something like:

select ...
where division_cd = case when myValue='A' then division_cd else myValue end
/
0
anumosesAuthor Commented:
The problem is I dont have value A stored in the table. Its like in ('E','W').
So A has to choose both E and W
0
sdstuberCommented:
select * from department
where division_cd = case when your_choice in ('E','W') then your_choice else division_cd end

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
yikes, I was really slow on that one.
0
slightwv (䄆 Netminder) Commented:
and?  You don't need the 'A' stored.  Look at what my case is doing.
0
ronythomCommented:
try this

case when division_cd  ="E" and division_cd  ="W" then "A"
else division_cd
end

0
sdstuberCommented:
you don't really need a case either,  just makes it more complicated


select * from department
where your_choice = 'A' or division_cd = your_choice
0
slightwv (䄆 Netminder) Commented:
Here is a simple test case showing how it works
drop table tab1 purge;
create table tab1(col1 char(1));

insert into tab1 values('E');
insert into tab1 values('1');
insert into tab1 values('2');
insert into tab1 values('3');
commit;


declare
	myvalue char(1);
	myCount number;
begin

myValue := 'E';
select count(1) into mycount from tab1 where col1 = case when myvalue = 'A' then col1 else myvalue end;
dbms_output.put_line('Got: ' || mycount);

myValue := 'A';
select count(1) into mycount from tab1 where col1 = case when myvalue = 'A' then col1 else myvalue end;
dbms_output.put_line('Got: ' || mycount);

end;
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
ronythom,

this doesn't make sense...

    case when division_cd  ="E" and division_cd  ="W" then "A"


how can division_cd be both E AND W

also note,strings should be enclosed in single quotes
0
sdstuberCommented:
extending on slightwv's example


myValue := 'A';
select count(1) into mycount from tab1 where myvalue = 'A' or col1 = myvalue;
dbms_output.put_line('Got: ' || mycount);



0
anumosesAuthor Commented:
@sdstuber

select * from department
where division_cd = case when your_choice in ('E','W') then your_choice else division_cd end

where division_cd = case when division_cd in ('E','W') then 'A' else division_cd end
-- Is this correct?
0
sdstuberCommented:
no it is not correct


just use this... much simpler

select * from department
where your_choice = 'A' or division_cd = your_choice
0
anumosesAuthor Commented:
The only stored values in the table are 'E' for East
'W' for West. And I am using suedo value A for All ie both E and W
0
sdstuberCommented:

to be more clear

this one is correct, but case isn't needed

     where division_cd = case when your_choice in ('E','W') then your_choice else division_cd end

----------------------------------------

this one is not correct

   where division_cd = case when division_cd in ('E','W') then 'A' else division_cd end
0
slightwv (䄆 Netminder) Commented:
>>-- Is this correct?

Does it work?

also, what was wrong with what I posted?  It is basically the same thing but only checking the 'A' value.
0
ronythomCommented:
try this

case when division_cd  ='E' or division_cd  ='W' then 'A'
else division_cd
end

0
sdstuberCommented:
ronythom,

no, that's wrong too

you can't compare division_cd to A,   A is not in the table.

If the choice is A then you're looking  everything
if the choice is E then you're looking for E
if the choice is W then you're looking for W




0
ronythomCommented:
sdstuber,

anumses wants to select 'A'for all ie 'E'or 'W'
I am not comapring this in the where clausse My case statement should be in side the SELECT
0
sdstuberCommented:
ah,  I see, ok

I think it's still wrong then because I don't read the question that way, but, maybe I'm incorrect.

I believe the intent is  anumoses will have a choice variable,  that choice will have one of 3 values
A, E, W

return all rows that match the choice.  E and W match exactly,  A matches everything
0
anumosesAuthor Commented:
@ronythom

try this
case when division_cd  ='E' or division_cd  ='W' then 'A'
else division_cd
end

I am choosing A then it has to display E and W
else
division_cd

0
slightwv (䄆 Netminder) Commented:
anmoses, sdstuber has provide a pretty simple method not using CASE.

He and myself have provided two alternatives using CASE.

What is not working with them?
0
anumosesAuthor Commented:
should it not be
when division_cd = 'A' then
division_cd in ('E','W')
else division_cd?

I am not able to put this in the right syntax
0
slightwv (䄆 Netminder) Commented:
>>I am not able to put this in the right syntax

If you pass in a parameter 'A' and you want that to select ALL rows in the table, pass in an 'E' or 'W' select only 'E' or 'W' codes, then the versions I referenced will do that.

Please run the test setup I provided in http:#a36309337 and explain what is not working in it.
0
sdstuberCommented:
>>> I am not able to put this in the right syntax

why are you trying to change what was already given?  What syntax are you trying to "put"

the only change I can think of is the "choice" variable.

slightwv called it myValue
I called it   your_choice

change the variable to whatever you are calling your choice of A, E, or W,  leave the rest of the query as is.


or,  post your REAL query along with some sample data and expected results
0
Jayesh AcharyaTechnichal ConsultantCommented:

set server output on;

declare
cursor c1 (your_choice varchar2) is
select count(*) row_count
from department
where division_cd in ( your_choice );

xEnterYourChoice varchar2(10);
xYourSearch      varchar2(10);
xNumberOfRows  number(9);

begin
       
      xEnterYourChoice := trim('&EnterYourChoice');
     
      -- this is the case to determine what values to put in your where clause
      -- for division_cd
      CASE  xEnterYourChoice
        WHEN 'A' THEN
          xYourSearch := '''E'',''W''';
        WHEN 'E' THEN
          xYourSearch := '''E''';
        WHEN 'W' THEN
          xYourSearch := '''W''';
        ELSE    
          xYourSearch := '''''';
      END CASE;
     
     -- this is the loop to find you data via the cursor
     For x in c1(trim(xEnterYourChoice)) loop
          xNumberOfRows:= x.row_count;
          dbms_output.Put_line(' You have '||xNumberOfRows||' number of rows for value '||xEnterYourChoice);
          dbms_output.Put_line(' You searched using division_cd in ('||xYourSearch||')');
     End loop;

       -- this is if the loop returns nothing
       IF xNumberOfRows is NULL THEN
         dbms_output.Put_line(' You have not found any rows for value '||xEnterYourChoice);
         dbms_output.Put_line(' You searched using division_cd in ('||xYourSearch||')');
       END IF;
End;
.
/

0
Jayesh AcharyaTechnichal ConsultantCommented:
duh typo



set server output on;

declare
cursor c1 (your_choice varchar2) is
select count(*) row_count
from department
where division_cd in ( your_choice );

xEnterYourChoice varchar2(10);
xYourSearch      varchar2(10);
xNumberOfRows  number(9);

begin
       
      xEnterYourChoice := trim('&EnterYourChoice');
     
      -- this is the case to determine what values to put in your where clause
      -- for division_cd
      CASE  xEnterYourChoice
        WHEN 'A' THEN
          xYourSearch := '''E'',''W''';
        WHEN 'E' THEN
          xYourSearch := '''E''';
        WHEN 'W' THEN
          xYourSearch := '''W''';
        ELSE    
          xYourSearch := '''''';
      END CASE;
     
     -- this is the loop to find you data via the cursor
     For x in c1(xYourSearch) loop
          xNumberOfRows:= x.row_count;
          dbms_output.Put_line(' You have '||xNumberOfRows||' number of rows for value '||xEnterYourChoice);
          dbms_output.Put_line(' You searched using division_cd in ('||xYourSearch||')');
     End loop;

       -- this is if the loop returns nothing
       IF xNumberOfRows is NULL THEN
         dbms_output.Put_line(' You have not found any rows for value '||xEnterYourChoice);
         dbms_output.Put_line(' You searched using division_cd in ('||xYourSearch||')');
       END IF;
End;
.
/

-- changed the variable for the loop
0
slightwv (䄆 Netminder) Commented:
>> IF xNumberOfRows is NULL THEN

A select count(*) will not return NULL so this is unnecessary.

Now about the code:  Technically I guess it is valid but why write all that code when you don't need to?
0
Jayesh AcharyaTechnichal ConsultantCommented:
slightwv answer i think is actually the one to go with if you only have a couple of choices to determine, but if you have a larger amount then I can see why you might want to put this as some function call.

0
Jayesh AcharyaTechnichal ConsultantCommented:
just trying to help the person walk through the solution, you are right you dont have to and its major overkill for what the person asked for, but I thought of lloking at the problem from the perspective that they will want some help in understanding how each part works and why...

I am not sure if I have done that but just wanted to give a different view to the same question

I put the null check, becasue i often find people are not looking for row counts but actual values form columns, so it is possible that your column had a null in it. Again just giving the person a breakdown onto how to resolve her problem. But saying that I would of probably done what you have done if faced with this issue
0
anumosesAuthor Commented:
thanks
0
sdstuberCommented:
just a note about the accepted solution.

the technique shown there

where division_cd = case when myValue='A' then division_cd else myValue end

Open in new window



is not reliable if division_cd might have NULL's

based on the question text, that's probably not the case here, but it's not a method I'd use for all data.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.