anumoses
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
I have values E- East
W- West
A- All
Need a case statement
table is department and column name is division cd
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
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
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
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
select * from department
where your_choice = 'A' or division_cd = your_choice
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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);
myValue := 'A';
select count(1) into mycount from tab1 where myvalue = 'A' or col1 = myvalue;
dbms_output.put_line('Got:
ASKER
@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?
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
just use this... much simpler
select * from department
where your_choice = 'A' or division_cd = your_choice
ASKER
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
'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.
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
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
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
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
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
ASKER
@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
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?
He and myself have provided two alternatives using CASE.
What is not working with them?
ASKER
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
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.
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
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))
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
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?
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
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
ASKER
thanks
just a note about the accepted solution.
the technique shown there
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.
the technique shown there
where division_cd = case when myValue='A' then division_cd else myValue end
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.
select ...
where division_cd = case when myValue='A' then division_cd else myValue end
/