Solved

Optional sql query where param

Posted on 2009-07-14
12
441 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
[X]
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
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Suggested Solutions

Title # Comments Views Activity
Updating a field based from a if exist.... 2 33
add more rows to hierarchy 3 34
Oracle cursor lifecycle inside procedure. 2 27
IIF in access query 19 40
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

756 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