Conditional Parameter in Where Clause

I'm trying to figure out a way to filter a dataset using a parameter only when the user enters a value for the parameter and to not apply the filter if the parameter is left blank (or null) by the user. I would like to do this within the WHERE clause of the SELECT statement to minimize the size of the dataset whenever possible. Is there such a thing as a default parameter value that equates to "any value"?

Nothing I have tried works (but I'm new to SQL, Report Server and the Visual Basic Development Environment).

Thanks in advance,

Chris

Rather than leaving the parameter unselected, you need to add an option with a value of NULL and text that matches your scenario e.g. blank, "All", "N/A", "Unspecified" etc. To do this you'll need to modify the query for the paramter dataset to:

SELECT id = NULL, name = 'All'
UNION ALL
SELECT id, name
FROM param_table

Then update your main query with the following WHERE clause

WHERE id = ISNULL(@.param, id)

so when the null option is selected the WHERE clause equates to id=id which is always true and hence all rows are returned.

Hope this helps.

|||

Thanks Adam,

I was not familiar with ISNULL. I got it to work sort of like I wanted it to by checking the "Allow Null Value" checkbox and making the default value NULL in the Report Parameters dialog box and then putting this in the WHERE clause:

WHERE LITEM.SIZE = ISNULL(@.Input_Size, LITEM.SIZE)

However, I could not figure out where to put the following statement (everything I tried resulted in an error - but I'm probably missing something obvious):

SELECT id = NULL, name = 'All'
UNION ALL
SELECT id, name
FROM param_table

...and therefore, the user must uncheck the NULL checkbox in order to enter a filter value and it's not real obvious that when NULL is checked, that the filter is not applied.

Thanks again for pointing me in the right direction!

Chris

|||

By your response it seems like your parameter is a textbox the user types into, is that correct?

My prerred way is to present the user a list of options i.e. a dropdown. In that case you don't get a null checkbox. The options in the dropdown can either be typed in on the paramter screen or can come from a dataset. The SELECT statement I provided is meant as an example of query used to populate such a dataset i.e. it includes a NULL option.

If you wish to use a textbox then you could alter your SQL query and rather than using ISNULL you could use an OR in your WHERE clause as follows

WHERE LITEM.SIZE = @.Input_Size
OR @.Input_Size = '' -- empty string

If LITEM.SIZE and @.Input_Size are integers then it gets a little more complicated. You'll need to experiment.

|||

Adam,

Thanks! It's now working just the way I wanted it to!

Chris Heitman

Conditional Joins

Hi, I have a query that needs to filter on certain data only if needed. eg.
(Assume tblStock and tblFilter are 1-to-1 on StockID)
DECLARE @.Min TINYINT , @.Max TINYINT
SELECT @.Min = 5 , @.Max = 10
SELECT
StockID
FROM
tblStock
INNER JOIN
tblFilter
ON
tblStock.StockID = tblFilter.StockID
AND
(
tblFilter.Value BETWEEN @.Min AND @.Max
OR
@.Min IS NULL OR @.Max IS NULL
)
Preferably I'd like to not do the join at all if either variable is null,
and I'd like it in a stored procedure so it's precompiled.
This is a simplified version of the query, in our system there are
aproximately 8 filters, which may or may not be required - and in any
combination.
I could, in theory create a stored proc for each combination of filter
inclusion - but this will be hard to maintain - especially if/when more
filters are introduced.
Thanks.
Rebecca.Please always include DDL with questions like this. It also helps to
include sample data and required results.
If Value is not nullable then maybe you just want to set @.min and @.max
to the min and max values for INT. Otherwise, use EXISTS:
SELECT stockid
FROM tblstock
WHERE EXISTS
(SELECT *
FROM tblfilter
WHERE tblStock.StockID = tblFilter.StockID
AND (stockid BETWEEN @.min AND @.max
OR @.min IS NULL
OR @.max IS NULL)) ;
David Portas
SQL Server MVP
--|||I have included the full ddl for the sample i've included.
Essentially what I want to do is put an IF around the Inner joins to exclude
the join if the parameters filtering the value are null.
Currently, we have the query dynamically built in the client, but this gives
compaliation overheads when various combinations are excluded/inculded and
we are investigating a pre-compiled version.
USE tempdb
/* Create tables */
IF OBJECT_ID('dbo.tblFilter2') IS NOT NULL DROP TABLE dbo.tblFilter2
IF OBJECT_ID('dbo.tblFilter1') IS NOT NULL DROP TABLE dbo.tblFilter1
IF OBJECT_ID('dbo.tblStock') IS NOT NULL DROP TABLE dbo.tblStock
CREATE TABLE dbo.tblStock ( StockID INT PRIMARY KEY CLUSTERED , Other
VARCHAR(5) NULL , Fields VARCHAR(5) )
CREATE TABLE dbo.tblFilter1 ( StockID INT PRIMARY KEY NONCLUSTERED , Value
INT , CONSTRAINT FK_dbo_tblFilter1_dbo_tblStock FOREIGN KEY ( StockID )
REFERENCES dbo.tblStock ( StockID ) )
CREATE TABLE dbo.tblFilter2 ( StockID INT PRIMARY KEY NONCLUSTERED , Value
DATETIME , CONSTRAINT FK_dbo_tblFilter2_dbo_tblStock FOREIGN KEY ( StockID )
REFERENCES dbo.tblStock ( StockID ) )
CREATE CLUSTERED INDEX CIX_dbo_tblFilter1 ON dbo.tblFilter1 ( Value )
CREATE CLUSTERED INDEX CIX_dbo_tblFilter2 ON dbo.tblFilter2 ( Value )
/* Populate sudo-random data */
INSERT INTO
dbo.tblStock
SELECT
N AS StockID
, NULL AS Other
, NULL AS Fields
FROM
dbo.tblNumbers /* contains n = 1 to 1,000,000 */
WHERE
N BETWEEN 1 AND 5000
INSERT INTO
dbo.tblFilter1
SELECT
StockID
, ( 5000 - StockID ) + 1 AS Value
FROM
dbo.tblStock
INSERT INTO
dbo.tblFilter2
SELECT
StockID
, DATEADD( d , -StockID , [Now] ) AS Value
FROM
dbo.tblStock
CROSS JOIN
( SELECT GETDATE() AS [Now] ) vwDate
/* Search Query */
GO
CREATE PROCEDURE
dbo.prRunFilter
@.Min INT
, @.Max INT
, @.FilerMonth DATETIME /* assumes midnight, first of month */
AS
SET DATEFORMAT YMD
DECLARE @.StartOfMonth DATETIME , @.EndOfMonth DATETIME
SELECT @.StartOfMonth = @.FilerMonth , @.EndOfMonth = DATEADD( ms , -3 ,
DATEADD( m , 1 , @.FilerMonth ) )
SELECT
tblStock.StockID
, tblFilter1.Value
, tblFilter2.Value
FROM
dbo.tblStock tblStock
INNER JOIN
(
SELECT
StockID
, Value
FROM
dbo.tblFilter1
WHERE
Value BETWEEN @.Min AND @.Max
) tblFilter1
ON
tblStock.StockID = tblFilter1.StockID
INNER JOIN
(
SELECT
StockID
, Value
FROM
dbo.tblFilter2
WHERE
Value BETWEEN @.StartOfMonth AND @.EndOfMonth
) tblFilter2
ON
tblStock.StockID = tblFilter2.StockID
GO
EXEC dbo.prRunFilter @.Min = 4490 , @.Max = 4500 , @.FilerMonth = {d
'2004-05-01'}|||I would create a view:
create view t1
as
SELECT
StockID
FROM
tblStock
INNER JOIN
tblFilter
ON
tblStock.StockID = tblFilter.StockID
in the procedure, I would
if @.Min IS NULL OR @.Max IS NULL
-- the plan for this may be simpler
select * from t1
else
-- this query might need a different plan
select * from t1
where tblFilter.Value BETWEEN @.Min AND @.Max
this way both the maintenance of the query is easy, as it's in a view,
and you have 2 plans|||Except that, as stated in my original post, we have 8 such filters...
This would result in 64 if/else statements and views to handle all the
combinations.
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1128437238.893216.20870@.o13g2000cwo.googlegroups.com...
> I would create a view:
> create view t1
> as
> SELECT
> StockID
> FROM
> tblStock
> INNER JOIN
> tblFilter
> ON
> tblStock.StockID = tblFilter.StockID
> in the procedure, I would
> if @.Min IS NULL OR @.Max IS NULL
> -- the plan for this may be simpler
> select * from t1
> else
> -- this query might need a different plan
> select * from t1
> where tblFilter.Value BETWEEN @.Min AND @.Max
> this way both the maintenance of the query is easy, as it's in a view,
> and you have 2 plans
>|||no - just one view, and yes, some if ... else statements.
Believe me or not, if you are selecting from big tables, then the
performance penalty of running the generic plan may be pretty high.
That, of course, has been said assuming that there are indexes that
could be used for some of your filters.

Conditional Column Filter

Hi
I have a field (FieldA) which I need to Filter on, based upon a parameter,
however the filter is only a substring of the FieldA.
e.g
FieldA = H2/Q3/10
the filter supplied maybe supplied as Q3, H2,or 10, I how to split up FieldA
in it's constituent parts, however in a WHERE clause I don't know how to
represent this.
i.e
If the parameter = Q3
then I would do :
WHERE SUBSTRING(FieldA,4,2) = 'Q3'
However if the param supplied was H2, then :
WHERE SUBSTRING(FieldA,4,2) = 'H2'
would not work?
I would therefore need to change the Column on how its being filtered on, is
there a way I can do this?
Kind Regards
RickyTry:
...
where '/' + FieldA + '/' like '%/' + @.s + '/%'
go
Do not expect SQL Server using properly an index by [FieldA] in case it
exists. You can google for "search arguments", for more info.
AMB
"ricky" wrote:

> Hi
> I have a field (FieldA) which I need to Filter on, based upon a parameter,
> however the filter is only a substring of the FieldA.
> e.g
> FieldA = H2/Q3/10
> the filter supplied maybe supplied as Q3, H2,or 10, I how to split up Fiel
dA
> in it's constituent parts, however in a WHERE clause I don't know how to
> represent this.
> i.e
> If the parameter = Q3
> then I would do :
> WHERE SUBSTRING(FieldA,4,2) = 'Q3'
> However if the param supplied was H2, then :
> WHERE SUBSTRING(FieldA,4,2) = 'H2'
> would not work?
> I would therefore need to change the Column on how its being filtered on,
is
> there a way I can do this?
> Kind Regards
> Ricky
>
>|||Any special reason for breaking the normal form by storing three values in a
single column? Have you considered properly normalizing the model?
Anyway, how about using wildcards:
WHERE FieldA like '%H2%'
For a more helpful answer, please provide DDL, sample data and expected
results.
ML
http://milambda.blogspot.com/|||Hi guys
thanks for the replies, won't wildcards be slow? Incidentally, if I use a
wildcard, when I search for 1 in H1Q11, won't it get with H1 or Q1?
Kind Regards
Ricky
"ML" <ML@.discussions.microsoft.com> wrote in message
news:E4D9EB2A-D620-4794-9220-3442B02B17C4@.microsoft.com...
> Any special reason for breaking the normal form by storing three values in
a
> single column? Have you considered properly normalizing the model?
> Anyway, how about using wildcards:
> WHERE FieldA like '%H2%'
> For a more helpful answer, please provide DDL, sample data and expected
> results.
>
> ML
> --
> http://milambda.blogspot.com/|||Using wildcards and functions in query conditions is slow, in most cases a
scan will be used. To improve the performance by using indexes first conside
r
normalizing the data.
ML
http://milambda.blogspot.com/|||ok ML, will do, thanks for the suggestion and tip.
Kind Regards
Ricky
"ML" <ML@.discussions.microsoft.com> wrote in message
news:547F6E76-0A5C-43ED-8649-34EEBD431307@.microsoft.com...
> Using wildcards and functions in query conditions is slow, in most cases a
> scan will be used. To improve the performance by using indexes first
consider
> normalizing the data.
>
> ML
> --
> http://milambda.blogspot.com/|||No, the answer Alesandro gave you handles that
(you specified slashes - where have they gone in this followup question?)
Bye
Jan
"ricky" <ricky@.ricky.com> wrote in message
news:Olme8JijGHA.1204@.TK2MSFTNGP02.phx.gbl...
> Incidentally, if I use a wildcard, when I search for 1 in H1Q11, won't it
get with H1 or Q1?|||Hi Jan
You're quite right, I forgot to place them in.
Well spotted.
Kind Regards
Ricky
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message
news:O52SBcijGHA.3440@.TK2MSFTNGP02.phx.gbl...
> No, the answer Alesandro gave you handles that
> (you specified slashes - where have they gone in this followup question?)
> Bye
> Jan
> "ricky" <ricky@.ricky.com> wrote in message
> news:Olme8JijGHA.1204@.TK2MSFTNGP02.phx.gbl...
it
> get with H1 or Q1?
>
>

COndition Spli - Error date condition

Dear friends,

I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...

I need to filter rows in a dataflow...

I created a condition spli to that... maybe there is a better solution...

And the condition is: Datex != NULL(DT_DATE)

(Some DATE != NULL)

[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.

What is wrong?

Regards,

Pedro

Hi Pedro,

What if you use IsNull(Datex) to define your condition? You could then use the default output for all rows where Datex is not Null.

Hope this helps,

Andy

|||

oooohhhh Men... soo simple... last week is being too hard for me... lot of work.... jesus!!!

Thanks!!

|||

Hi Pedro,

You're welcome! Don't be so hard on yourself. SSIS isn't intuitive and I've been working with it a while. We're all still learning!

Andy