Dynamic Query expression

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.
--CorySorry 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"|||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"
"TomP" wrote:
> 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"
>|||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 & "'")
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Cory" <Cory@.discussions.microsoft.com> wrote in message
news:E6AEFEFE-4EBE-4280-AA24-5CCDDC4E0E43@.microsoft.com...
> 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"
> "TomP" wrote:
>> 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"
>>|||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
"Bruce L-C [MVP]" wrote:
> 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 & "'")
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Cory" <Cory@.discussions.microsoft.com> wrote in message
> news:E6AEFEFE-4EBE-4280-AA24-5CCDDC4E0E43@.microsoft.com...
> > 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"
> >
> > "TomP" wrote:
> >
> >> 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"
> >>
> >>
>
>|||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.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Cory" <Cory@.discussions.microsoft.com> wrote in message
news:53CF6DCA-0BDA-4FB4-B9BD-B646D9246CCF@.microsoft.com...
> 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
> "Bruce L-C [MVP]" wrote:
>> 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 & "'")
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Cory" <Cory@.discussions.microsoft.com> wrote in message
>> news:E6AEFEFE-4EBE-4280-AA24-5CCDDC4E0E43@.microsoft.com...
>> > 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"
>> >
>> > "TomP" wrote:
>> >
>> >> 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"
>> >>
>> >>
>>|||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
"Bruce L-C [MVP]" wrote:
> 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.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Cory" <Cory@.discussions.microsoft.com> wrote in message
> news:53CF6DCA-0BDA-4FB4-B9BD-B646D9246CCF@.microsoft.com...
> > 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
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> 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 & "'")
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Cory" <Cory@.discussions.microsoft.com> wrote in message
> >> news:E6AEFEFE-4EBE-4280-AA24-5CCDDC4E0E43@.microsoft.com...
> >> > 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"
> >> >
> >> > "TomP" wrote:
> >> >
> >> >> 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"
> >> >>
> >> >>
> >>
> >>
> >>
>
>