Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to get this query ?

Posted on 2014-07-14
15
Medium Priority
?
283 Views
Last Modified: 2014-07-22
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
0
Comment
Question by:deve_thomos
15 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40194891
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
 
LVL 10

Expert Comment

by:aboo_s
ID: 40194905
$value= 'OPEN' ;
$myquery = "SELECT eid,status FROM TEST12 WHERE STATUS='$value'  ;"  ;
0
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 40194909
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:deve_thomos
ID: 40194960
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40194964
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40194973
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
 

Author Comment

by:deve_thomos
ID: 40195200
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40195218
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40195244
>>> 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
 

Author Comment

by:deve_thomos
ID: 40195475
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40195503
>>> 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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40196005
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40196298
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40196882
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
 

Author Closing Comment

by:deve_thomos
ID: 40212833
thanks alot...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month14 days, 7 hours left to enroll

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question