Link to home
Start Free TrialLog in
Avatar of anumoses
anumosesFlag for United States of America

asked on

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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

something like:

select ...
where division_cd = case when myValue='A' then division_cd else myValue end
/
Avatar of anumoses

ASKER

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
select * from department
where division_cd = case when your_choice in ('E','W') then your_choice else division_cd end

yikes, I was really slow on that one.
and?  You don't need the 'A' stored.  Look at what my case is doing.
try this

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

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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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);



@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?
no it is not correct


just use this... much simpler

select * from department
where your_choice = 'A' or division_cd = your_choice
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

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
>>-- 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.
try this

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

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




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
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
@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

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?
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
>>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.
>>> 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

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;
.
/

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
>> 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?
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.

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
thanks
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.