Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Optional sql query where param

Posted on 2009-07-14
12
439 Views
Last Modified: 2013-12-07
I have an optional parameter in my sql query.   How do i code the "where" statement in the query to (1) when the optional parameter is filled in to use it,  and when its blank or null pull "everything"
example:

where student = parm1 else student = <pull all students>
0
Comment
Question by:Mike Johnson
12 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 42 total points
ID: 24848609
where ( student = parm1 or param1 is null )
0
 
LVL 4

Assisted Solution

by:fsouzabrasil
fsouzabrasil earned 42 total points
ID: 24848723
where ( student = parm1 or param1 is null or param1='' )
0
 
LVL 14

Expert Comment

by:shru_0409
ID: 24848893
where student = decode (student,parm1,parm1, student)

try this
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 32

Expert Comment

by:awking00
ID: 24849578
Is this part of a procedure? If so, can you post it?
0
 
LVL 3

Expert Comment

by:BrightApollo
ID: 24852055
There's always

WHERE student LIKE parm1

Assuming you set the parm1 default to the wildcard for your db, any valid input overwrites the wildcard.  Otherwise, you get everything.

--#

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 41 total points
ID: 24853117
where student like nvl(param1, student)
0
 
LVL 2

Expert Comment

by:uaexpert
ID: 24950179
It seems this question is not concerned to SQL.
You just need to provide user-friendly query builder in your program. If user add some condition(s) - that builder will create necessary SQL statement if not - you can use some "default" statement (SELECT * FROM Sometable).
0
 
LVL 3

Expert Comment

by:BrightApollo
ID: 25158703
awking00 had the better solution, in my professional opinion.  it stands alone, it's extremely simple and compact, and requires no OR logic.  So, I object to the points split.  I really think awking00 has it.
0
 
LVL 32

Expert Comment

by:awking00
ID: 25159087
BrightApollo,
Thanks for your support. The only problem I see is that this is an Oracle solution and an awful lot of questioners use this zone (annoyingly so) when they're working with another RDBMS. Even so, I'm sure there is corresponding syntax (e.g. IFNULL) in other databases that could be applied. The points aren't that important to me. I just want to see questioner's problems resolved and wish that they would acknowledge the same.
0
 
LVL 3

Expert Comment

by:BrightApollo
ID: 25159124
Given the context of the forum, you provided an optimal Oracle solution.  I understand the greater context of the problem, but "SQL syntax" is the appropriate vendor-neutral forum.  I just wanted to stipulate it for the record, in the event someone looks at the thread and actually wants the Oracle solution.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

789 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