Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Optional sql query where param

Posted on 2009-07-14
12
Medium Priority
?
451 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 168 total points
ID: 24848609
where ( student = parm1 or param1 is null )
0
 
LVL 4

Assisted Solution

by:fsouzabrasil
fsouzabrasil earned 168 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
Vim Reference Guide

Vim is a powerful text editor favored by many sysadmins and developers - here are some commands that you'll want to keep in your back pocket!

 
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 164 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

What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

Question has a verified solution.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

721 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