Solved

Optional sql query where param

Posted on 2009-07-14
12
432 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 142

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
 
LVL 31

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 31

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 31

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
This video shows how to recover a database from a user managed backup

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now