Dynamic Query expression

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cory - 28 Oct 2005 15:05 GMT

I am receiving the "expression expected" error when trying to include the
following in an expression for a sql statement. I don't receive it without
the IIF statement & do if I include it:

"AND (table.field = @Team OR @Team = '(All Teams') " &
IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field = 3 ")
" ORDER BY Year, Weeknum, OpenDate"

Thank you for any help.

--Cory

Reply to this Message

TomP - 28 Oct 2005 15:55 GMT

Sorry if this is too obvious, but have you remembered the 1st '='?

Your expression should read

="AND (table.field = @Team OR @Team = '(All Teams') " &
IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field
= 3 ")
" ORDER BY Year, Weeknum, OpenDate"

Reply to this Message

Cory - 28 Oct 2005 16:06 GMT

Hi. I only sent part of the query... Here's more of the query - I just
removed field names etc. Everything works until I add in the iif statement.

="SELECT " &
"table.field, table.field etc" &
"FROM table " &
"GROUP BY table.field, table.field etc" &
"HAVING table.field IN ('text value') " &
"AND table.date >= @StartDate " &
"AND table.date <= @EndDate " &
"AND (table.field = @Team OR @Team = '(All Teams)') " &
IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field = " &
Parameters!Category.Value & "")
" ORDER BY Year, Weeknum, OpenDate"

> Sorry if this is too obvious, but have you remembered the 1st '='?
>
[quoted text clipped - 4 lines]
> = 3 ")
> " ORDER BY Year, Weeknum, OpenDate"

Reply to this Message

Bruce L-C [MVP] - 28 Oct 2005 16:15 GMT

What is biting you here is typical of dynamic sql. I assume category is a
string. It needs to be enclosed in single quotes. When you have a query
paramter RS is handling all of this for you. When you assemble the string
yourself you need to enclude any necessary single quotes.

AND table.field = '" & Parameters!Category.Value & "'")

Signature

Bruce Loehle-Conger
MVP SQL Server Reporting Services

> Hi. I only sent part of the query... Here's more of the query - I just
> removed field names etc. Everything works until I add in the iif
[quoted text clipped - 21 lines]
>> = 3 ")
>> " ORDER BY Year, Weeknum, OpenDate"

Reply to this Message

Cory - 28 Oct 2005 18:26 GMT

Do you have to use the value of the parameter in the = statement?

What I have is a situtation where the user is allowed to choose from:

All Categories
Exclude Category 3
Only Category 3

The actual value of the field being used in for query is the character 3
(varchar).

So what I ended out trying was the following:

IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.category_id
= '" & 3 & "'")

and I also tried:

IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.category_id
= '3'")

but neither worked. I can't use the actual value of the parameter because
I'm going to need 2 if statements, one that is equal to 3 and one that is not
equal to 3. So 3 would need to be the value twice... which of course won't
work.

I get the feeling I'm making this more complicated than necessary. If you
have suggestions I'll take any.

Thanks again.

--Cory

> What is biting you here is typical of dynamic sql. I assume category is a
> string. It needs to be enclosed in single quotes. When you have a query
[quoted text clipped - 28 lines]
> >> = 3 ")
> >> " ORDER BY Year, Weeknum, OpenDate"

Reply to this Message

Bruce L-C [MVP] - 28 Oct 2005 19:58 GMT

Those are the labels, not the value correct? Remember that the values do not
have to equal label. You can accomplish this without dynamic sql.

I have three label, value pairs: All Categories, 0 Exclude Category
3, -1 Only Category 3

myfield = @CategoryParam or @CategoryParam = 0 or (@CategoryParam = -1 and
myfield != 3)

For the above to work 0 and -1 have to not be valid categories.

So, take a look at the three choices. If you select All Categories then
@CategoryParam will = 0 and all your categories will returned. If Only
Category 3 is selected then @CategoryParam will = 3. Then finally the last
one handles returning everything except where the category equal 3.

Now you can totally get away from dynamic SQL.

Signature

Bruce Loehle-Conger
MVP SQL Server Reporting Services

> Do you have to use the value of the parameter in the = statement?
>
[quoted text clipped - 66 lines]
>> >> = 3 ")
>> >> " ORDER BY Year, Weeknum, OpenDate"

Reply to this Message

Cory - 31 Oct 2005 18:28 GMT

Thank you Bruce. That was exactly what I was looking for. I had a suspicion
I was making it more difficult than necessary.

Thanks again.

--Cory

> Those are the labels, not the value correct? Remember that the values do not
> have to equal label. You can accomplish this without dynamic sql.
[quoted text clipped - 84 lines]
> >> >> = 3 ")
> >> >> " ORDER BY Year, Weeknum, OpenDate"

Reply to this Message