How to get this query ?

HI EXPERT,

create table TEST12(eid number,STATUS varchar2(50));

insert into TEST12 values(1,'Open');
insert into  TEST12 values(2,'Closed');

Open in new window

I Need a query,
in where  CLAUSE if i pass  status value as  'Open'

i will get all records whose status  is Open  

EXPEXTED RESULT
EID         STATUS
1            open

if I pass status value as 'Closed'

Expexted
EID         STATUS
2            Closed

if I PASS values 'All'
all records should show.
Expected

EID    STATUS
1       open
2       Cosed



Regards
Thomos
deve_thomosAsked:
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.

sdstuberCommented:
assuming you have a value v_status as your input parameter try this...

select * from test12 where v_status = 'All' or status = v_status;
0
aboo_sCommented:
$value= 'OPEN' ;
$myquery = "SELECT eid,status FROM TEST12 WHERE STATUS='$value'  ;"  ;
0
Nathan RileyFounderCommented:
Variable options:

This will pull open
$variable = 'open'

This will pull closed
$variable = 'closed'

This will pull all
$variable = 'open','closed'

select *
from test12
where status in ($variable);

Open in new window

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.

deve_thomosAuthor Commented:
Hello Expert,

my requirement is not exactly that,

Please see below


select *
from test12
where  (:Status is null or trim(lower(STATUS)) = lower(:Status)) ;

if i am  passing 'open' i am able to see all records whose status is open , similarly if i am  passing 'close' i am able to see all records whose status is 'close', if i pass 'All' then i should get all records
Regards
Thomos
0
sdstuberCommented:
same idea as I originally posted, all you need to do is add an OR clause for your variable being "All"


select *
from test12
where  (:Status is null or trim(lower(STATUS)) = lower(:Status)  or :Status = 'All');


or, if "All" might be in mixed case then apply lower or upper just as you did in your other conditions...

select *
from test12
where  (:Status is null or trim(lower(STATUS)) = lower(:Status)  or lower(:Status) = 'all');
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:
also you might want to note that not passing in any value (i.e. using NULL) will result in the same as using 'All'

because of the OR condition :Status is null

if you don't want that, then simply remove it


select *
from test12
where  (trim(lower(STATUS)) = lower(:Status)  or :Status = 'All');


or, if "All" might be in mixed case then apply lower or upper just as you did in your other conditions...

select *
from test12
where  (trim(lower(STATUS)) = lower(:Status)  or lower(:Status) = 'all');
0
deve_thomosAuthor Commented:
Hello Expert,
There is no  status value for 'all'. but i am passing all in parameter in that no records are coming.

select *
from test12
where  (trim(lower(STATUS)) = lower(:Status)  or lower(:Status) = 'all');
if i query like this i should get  below
EID    STATUS
1       open
2       Cosed

Regards
Thomos.
0
sdstuberCommented:
I'm not sure what you are asking in http:#a40195200.

are you trying to say

select *
from test12
where  (trim(lower(STATUS)) = lower(:Status)  or lower(:Status) = 'all');


doesn't work for you? if so, what are you doing?  it works for me...



SQL> create table TEST12(eid number,STATUS varchar2(50));

Table created.

SQL> insert into TEST12 values(1,'Open');

1 row created.

SQL> insert into  TEST12 values(2,'Closed');

1 row created.

SQL> variable Status varchar2(10)
SQL> exec :status := 'all';

PL/SQL procedure successfully completed.

SQL> select *
  2  from test12
  3  where  (trim(lower(STATUS)) = lower(:Status)  or lower(:Status) = 'all');

       EID STATUS
---------- --------------------------------------------------
         1 Open
         2 Closed

Open in new window



>>> There is no  status value for 'all'.

I know, that's why I don't look at the table value for 'all' but instead check the variable you are using.


and, just to be thorough,  using 'open' and 'closed' work as intended too


SQL> exec :status := 'open';

PL/SQL procedure successfully completed.

SQL> select *
  2  from test12
  3  where  (trim(lower(STATUS)) = lower(:Status)  or lower(:Status) = 'all');

       EID STATUS
---------- --------------------------------------------------
         1 Open

SQL> exec :status := 'closed';

PL/SQL procedure successfully completed.

SQL> select *
  2  from test12
  3  where  (trim(lower(STATUS)) = lower(:Status)  or lower(:Status) = 'all');

       EID STATUS
---------- --------------------------------------------------
         2 Closed

Open in new window



if these are not the results you are getting, please post exactly what you are doing.
0
sdstuberCommented:
>>> I know, that's why I don't look at the table value for 'all' but instead check the variable you are using.


I probably should have phrased that more clearly.
What I meant was...

I don't EXCLUSIVELY check the table value,  rather the OR clause is what makes the query work.

Either the table value matches the passed in value (OPEN or CLOSED)  OR, the passed in value is ALL.

If, for some reason, you ever did create a value of ALL, the query would still work.  I'm not expecting you to do that.  I'm just saying it wouldn't require the query to change.
0
deve_thomosAuthor Commented:
Hi ststuber,
i think you are not geeting my question ,
its very simple,
need a simple query if i pass  'open' i will get all records whose status is open , if i pass 'Close' i  will get all records  whose status is closed , if i pass 'all' i should get all records

see i i was expecting like this ,if i give status as 'all' i will get all records
select * from test12 where status=all(select status from test12 where STATUS=:status);
;
0
sdstuberCommented:
>>> need a simple query if i pass  'open' i will get all records whose status is open , if i pass 'Close' i  will get all records  whose status is closed , if i pass 'all' i should get all records

yes, I know - did you try what I posted?

 it does EXACTLY WHAT YOU ASKED FOR.

if it does not, then post exactly what you tried, just as I did in my posts above.

I've replicated the requested results, you can test what I've done by following the same steps.

If I've made a mistake show me with your results and I'll try to fix whatever I've done wrong.

I'm not sure why you think you want a subquery for this problem.
I tested what you posted, it doesn't do what you described.
Using  status = all()  like you showed makes the :status variable sort of irrelevant.

you could use 'this_does_not_exist' and it would return all rows.
similarly, if you used 'closed'  you'll get both Open and Closed
0
Mark GeerlingsDatabase AdministratorCommented:
The simplest way to get "ALL" records returned by an Oracle query is to use the multi-character wild card in Oracle which is the "%" sign.  So, if you pass in 'open' as a parameter value, you will get only the records that match that.  Similarly if you pass in 'closed', you will get only the records that match that value.  Oracle doesn't recognize the word "all" as having any special meaning, so you have to use "%" to get all records returned *AND* your query must use "like" and not "=" as the comparison operator.

For example:
select *
 from test12
 where  trim(lower(STATUS)) like lower(:Status);

Note: you didn't ask about this, but I really don't like seeing SQL operators: "trim", "lower" etc, applied to database column values in Oracle queries.  These can make your queries *VERY* slow, since they force Oracle to read every record in the table, and apply these operators to every record, unless you have function-based indexes that *EXACTLY* match this condition.
0
PortletPaulfreelancerCommented:
http:#a40195200

"Hello Expert,
There is no  status value for 'all'. but i am passing all in parameter in that no records are coming.
"

We understand there is no actual status = 'All'

That is why the solution is to NOT compare status to the variable when the variable is 'All'

Try just this (& nothing else than this):

select * from TEST12 where 'All' = 'All';
you should get:
| EID | STATUS |
|-----|--------|
|   1 |   Open |
|   2 | Closed |

Open in new window

So if the variable has a name of :YourVariable and that variable is assigned the value 'All' then

select * from TEST12 where :YourVariable = 'All';

will select all records (because we are NOT comparing the variable to the field Status (yet).

To complete the requirement we also compare the variable to the status field, like this:

select *
from TEST12

where    ( status = :YourVariable      OR    :YourVariable = 'All'  )
;

So;
if :YourVariable = 'Open'   you will only get Open records   (compare variable to status field)
if :YourVariable = 'Closed' you will only get Closed records  (compare variable to status field)
if :YourVariable = 'All'        you will get all records   (compare variable to constant held in the query)
0
sdstuberCommented:
I intentionally did not discuss the performance ramifications of the functions, but if the data is mixed then they are kind of unavoidable although (as you noted) function-based indexes could help.  

Since they were brought up though, I recommend NOT using the LIKE method.  It hides information from the optimizer (probably minor though in this usage.)  More importantly , in this case using OR not only matches the functionality the syntax itself matches the description of what was asked.

Is the status Open/Close?
OR
Did the user request All?

markgeer, Portlet Paul -
I don't think any explanations by any of us are going to help.  If I got it right then it's simply a matter of the asker trying out what I posted for himself and confirming it works.

If I got it wrong, then all commentary trying to explain how what is posted should work don't apply and we're back to my request for him to show what he has tried with the data and results expected.
0
deve_thomosAuthor Commented:
thanks alot...
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.