Empty Column: filters

Showing posts with label filters. Show all posts

Wednesday, March 7, 2012

Enclosing multiple statements in a variable

Hi all;

How do I enclose multiple filters in a variable, for instance how would I put the following filter into a variable and also is it actually possible or do I have to do something else before performing this type of operation:

tel1 like '072%' or tel1 like '+27 72%' or tel1 like '072-%'
or tel1 like '+2772%' or tel1 like '72%'
and len(tel1) > 7
or tel2 like '072%' or tel2 like '+27 72%' or tel2 like '072-%'
or tel2 like '+2772%' or tel2 like '72%'
and len(tel2) > 7
or tel3 like '072%' or tel3 like '+27 72%' or tel3 like '072-%'
or tel3 like '+2772%' or tel3 like '72%'
and len(tel3) > 7
or tel4 like '072%' or tel4 like '+27 72%' or tel4 like '072-%'
or tel4 like '+2772%' or tel4 like '72%'
and len(tel4) > 7
or tel5 like '072%' or tel5 like '+27 72%' or tel5 like '072-%'
or tel5 like '+2772%' or tel5 like '72%'
and len(tel5) > 7
or tel_other like '072%' or tel_other like '+27 72%' or tel_other like '072-%'
or tel_other like '+2772%' or tel_other like '72%'
and len(tel_other) > 7

the problem is that it's got a couple of apostrophes which when declaring variables pulls it out of that mode, I have looked on the internet but can't seem to find anythingthats no problem - just use an extra quote for each quote

set @.statement = N'master.dbo.xp_smtp_sendmail @.subject= N''Purchase Failure (Dont Reply To This Mail)'''|||Hi all;
thanks for the assistance how would I use the variable in a where section in other words it would follow as this

declare @.var varchar(10)
set @.var = 'multiple line like and or filters'
select * from table1
where @.var

thanks all, reaally appreciate the help

cheers|||Hi all;
thanks for the assistance how would I use the variable in a where section in other words it would follow as this

declare @.var varchar(10)
set @.var = 'multiple line like and or filters'
select * from table1
where @.var

thanks all, reaally appreciate the help

cheers

for this u need to use dynamic sql something like:

declare @.var varchar(10),@.Sql nvarchar(1000)
set @.var = 'userid>1000'
set @.sql='select * from users
where '+@.var
exec sp_executesql @.sql|||Hi all;

I am trying the following query:

declare @.072 varchar(5000),@.073 varchar(5000),@.Sql nvarchar(1000)
set @.072 = 'tel1 like ''072%'' or tel1 like ''+27 72%'' or tel1 like ''072-%''
or tel1 like ''+2772%'' or tel1 like ''72%''
and len(tel1) > 7
or tel2 like ''072%'' or tel2 like ''+27 72%'' or tel2 like ''072-%''
or tel2 like ''+2772%'' or tel2 like ''72%''
and len(tel2) > 7
or tel3 like ''072%'' or tel3 like ''+27 72%'' or tel3 like ''072-%''
or tel3 like ''+2772%'' or tel3 like ''72%''
and len(tel3) > 7
or tel4 like ''072%'' or tel4 like ''+27 72%'' or tel4 like ''072-%''
or tel4 like ''+2772%'' or tel4 like ''72%''
and len(tel4) > 7
or tel5 like ''072%'' or tel5 like ''+27 72%'' or tel5 like ''072-%''
or tel5 like ''+2772%'' or tel5 like ''72%''
and len(tel5) > 7
or tel_other like ''072%'' or tel_other like ''+27 72%'' or tel_other like ''072-%''
or tel_other like ''+2772%'' or tel_other like ''72%''
and len(tel_other) > 7'

set @.073 = 'or tel1 like ''073%'' or tel1 like ''+27 73%'' or tel1 like ''073-%''
or tel1 like ''+2773%'' or tel1 like ''73%''
and len(tel1) > 7
or tel2 like ''073%'' or tel2 like ''+27 73%'' or tel2 like ''073-%''
or tel2 like ''+2773%'' or tel2 like ''73%''
and len(tel2) > 7
or tel3 like ''073%'' or tel3 like ''+27 73%'' or tel3 like ''073-%''
or tel3 like ''+2773%'' or tel3 like ''73%''
and len(tel3) > 7
or tel4 like ''073%'' or tel4 like ''+27 73%'' or tel4 like ''073-%''
or tel4 like ''+2773%'' or tel4 like ''73%''
and len(tel4) > 7
or tel5 like ''073%'' or tel5 like ''+27 73%'' or tel5 like ''073-%''
or tel5 like ''+2773%'' or tel5 like ''73%''
and len(tel5) > 7
or tel_other like ''073%'' or tel_other like ''+27 73%'' or tel_other like ''073-%''
or tel_other like ''+2773%'' or tel_other like ''73%''
and len(tel_other) > 7'

set @.sql='select * from fl1_pcode
where '+@.072 +@.073
exec sp_executesql @.sql

but then i get the following error:

Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near 'tel'.

Firstly is this the right method of doing it and how does this error occur, I have tried only using the 072 variable and it pulls out a result fine, but the minute I add the second variable or insert the info from the second variable into the first (make the first variable longer) I get that error.

THanks 4 all the help so far ;)|||hi
check out you concatenation and unicode - put a space between two vars
and specify unicode..(N)

set @.sql= N'select * from fl1_pcode
where '+@.072 +' '+@.073

exec sp_executesql @.sql

if it still gives trouble syntax wise, instead of executing @.sql, select it,
and past it into a new query window - then youll be able to fine the syntax error location and make amends,
goodluck,
Des|||Hi Desmond;

Sorry about my ignorance but how would i pass it into a new query window ?

Thanks|||spelling was meant to be "paste" on my side -
in query analyzer, just open another window, and paste the results from
your select @.sql query, then see if it runs - amend if necessary
des|||Where should I look for the information that it compiles ?

Thanks

Wednesday, February 15, 2012

Empty multi-value parameter

Several people have asked about this, but I have not seen a response that works. I have several parameters that I want to use as filters. If a particular filter parameter is not entered, the report should not apply a filter on that criterion. It works fine to create a single value parameter which allows null, and use the following in SQL:

WHERE ( [Field1] = @.Field1_Filter OR @.Field1_Filter IS NULL )

How can the same effect be achieved for a multi-value parameter? If you try to create a multi-value parameter which allows null, the report builder gives an error. I can't use "allow blank value" because some of my parameters are integer parameters. Without the allow null, the report generator requires me to enter at least one value. So I don't know how you can get a value of "not entered" into a multi-value parameter.

I also don't know how you would check for the "not entered". Someone suggested the following SQL:

WHERE ( [Field1] IN (@.Field1_Filter) OR @.Field1_Filter IS NULL )

This generates an error when generating the report if multiple values are entered for the parameter (the normal condition). The error is " An expression of non-boolean type specified in a context where a condition is expected, near ',' ".

How is this supposed to be done?

Thanks for any insight

Would the following work?

WHERE (

[Field1] in (@.Field1_Filter)

OR (select count(*) from table where [Field1] in (@.Field1_Filter)) = 0

)

Or if that's too expensive, you might add an extra query parameter and pass in the following expression:

=Parameters!Field1_Filter.Value.Count

Then your SQL could read:

WHERE ( [Field1] IN (@.Field1_Filter) OR @.Field1_FilterCount = 0 )

|||

Dont use the filter in the SQL query. Instead keep your SQL query as a simple select statement and implement the filter in the dataset Filter using expressions like this:

For example, if your SQL query is "SELECT * FROM Table 1 WHERE ( [Field1] IN (@.Field1_Filter) OR @.Field1_Filter IS NULL )", change it to "SELECT * FROM Table1"

Now edit the dataset and go to Filter tab.

Under expression type this:

=IIf(Parameters!Field1_Filter.Value <> Nothing and Parameters!Field1_Filter.Count > 0, Fields!Field1.Value, 1)

Under Operator select "IN"

Under Value type this:

=IIf(Parameters!Field1_Filter.Value <> Nothing and Parameters!Field1_Filter.Count > 0, Parameters!Field1_Filter.Value, 1)

This way, when the filter parameter is null/nothing or count = 0, 1=1 will be evaluated which means no filter condition is applied.

Shyam

|||Thanks for the suggestions.

furmangg:

Your first suggestion wouldn't work because it would return everything anytime the filter didn't match, not just when the filter is empty.

I didn't think about using the count of the multivalue parameter. I think it's an array, so that could work either in the query (using another query parameter) or in a report filter. But how do I get an empty array in there? I'm using integer parameters. I can't set the parameter to allow null, and when I preview the report and leave the parameter blank, it tells me to enter at least one value. I tried it with string parameters, and the report just doesn't run in preview if you don't enter any values, even if you choose "allow blank".

How do you specify "empty" for a multivalue parameter?
|||Anyone have any ideas? Can someone at least confirm that this is a problem and not some screwy setup on my machine?
|||

If you set allow null or allow blank for your multi-valued parameter, one of those values must be null or blank. For example, if your parameter is getting its values from a query, make the query to return one more row with blank value or null value. Or if your list of values is non-queried, add another value to the list with blank or null value.

Shyam

|||Has anyone found a solution for this as I am having the same problem.

I have a multi-value parameter (Dim1Code) that is populated from a query:

SELECT
Dim1Code,
Dim1Code + ', ' + Description AS Description
FROM
Dim1

For some clients the query returns no data (which is fine) and so the drop down list is empty.

I have another parameter (EmployeeList) based on the following query:

SELECT
E.EmployeeCode,
E.EmployeeCode + ' - ' + E.Lastname + ', ' + E.Firstnames AS Name
FROM
Employee E
WHERE
Dim1Code IN (@.Dim1Code)

If the Dim1Code list is empty then it should return all employees, and I have not been able to find a way to get this working.

I have tried adding a union statement to the first query to also return a null row, but it wont let me set the "Allow Null" property on my parameter (I get an error as this is an invalid option for multi-value parameters).

If I set "Allow Blank" for my parameter and then run the report I am told that I need to select a value for the Dim1Code parameter.

If I union my first select with a hard coded value like "<None>" and change my second select statement to:

WHERE
Dim1Code IN (@.Dim1Code) OR @.Dim1Code = '<None>'

I then get an error when I try to run the report:

"An expression of non-boolean type specified in a context where a condition is expected, near ','"

I have tried the filtering options suggested further up in this thread and none of them worked for me either.

Is there really no way of having a valid "None" or "Blank" or "Null" option for multi-value parameters?

Why are multi-value parameters ALWAYS required to have a value?
|||

How about the following for the parameter query:

SELECT
Dim1Code,
Dim1Code + ', ' + Description AS Description
FROM
Dim1

union all

SELECT
'' as Dim1Code,
'-All Codes-' AS Description

WHERE (select count(*) from Dim1) = 0

Then the main query would read:

SELECT
E.EmployeeCode,
E.EmployeeCode + ' - ' + E.Lastname + ', ' + E.Firstnames AS Name
FROM
Employee E
WHERE
Dim1Code IN (@.Dim1Code)

OR '' in (@.Dim1Code)