Solved

a way to filter using parameters including nul/blanket records?

Posted on 2010-09-20
4
253 Views
Last Modified: 2012-05-10
I am using a paramter to query the table. ProjectTable(id,status,team).

Select * From projectable where team in (@team)

OK this is fine but I also would to include null and blanket value when I select all teams from the parameters.

How can I do that?
0
Comment
Question by:dkim18
[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
4 Comments
 
LVL 8

Assisted Solution

by:nognew
nognew earned 100 total points
ID: 33718297
Hi there!
 how about that:
Cheers,
t.
Select * From projectable where team in (@team) or team is null

Open in new window

0
 
LVL 11

Accepted Solution

by:
aelliso3 earned 300 total points
ID: 33718657

If you only want to include null and blank values when you select all, then there's a couple of steps to it. The suggestion above will include NULL values when any query is returned.

In your query that provides the paramater values, do a union to add in the first row like below:

SELECT id=999, Status='', team = 'SELECT ALL'
UNION
SELECT id, status, team FROM projectable

This will add one more value to your list that will bring back everything if selected. Otherwise, you'll only get what you selected without the Nulls and blanks.

Select id, active, team
From projectable
where id in (@team)
     or @team = 'SELECT ALL'
0
 
LVL 15

Assisted Solution

by:AmmarR
AmmarR earned 100 total points
ID: 33718718
Dear dkim18

check the code below,
SELECT * FROM projectable  where team in (@pteam) or (@pteam is null and team is null)

Open in new window

0
 

Author Closing Comment

by:dkim18
ID: 33833865
o
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

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. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

617 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