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"
> >> >>
> >> >>
> >>
> >>
> >>
>
>

dynamic query "expression expected" error

I'm using the following SQL and receiving: "The expression for the
query 'NOL' contains an error: [BC30201] Expression expected."
="SELECT C.CardholderLastName, C.CardholderFirstName,
C.CardNumberLast4Digits, C.CUDiscoveryDate,
C.EstimatedLossAmount_Quantity LossAmount, C.CUSTOMERREFERENCE,
(SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
X.REPORTINGTYPE = 'FraudTypeGrouping1' AND X.NSID = Y.LONSID AND X.ID = Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE = CHAR(C.FRAUDTYPE)) as FRAUDTYPE,
(SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
X.REPORTINGTYPE = 'TransactionType' AND X.NSID = Y.LONSID AND X.ID = Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE = CHAR(C.TRANSACTIONTYPE)) as TRANSACTIONTYPE,
(SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
X.REPORTINGTYPE = 'BankCardProgram' AND X.NSID = Y.LONSID AND X.ID = Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE = CHAR(C.CARDPROGRAM)) as CARDPROGRAM, 'ALL' as ALL_TEST
FROM APX.ACUWEBDATAENTRYPLASTICCARD AS C, APX.AICPERLEGALSTRUCTURE AS D
WHERE C.CUSTOMERREFERENCE = D.CUSTOMERREFERENCE " &
IIF(Parameters!CONTRACTNUMBER.Value = 'ALL', "", "AND
C.CUSTOMERREFERENCE = '" & Parameters!CONTRACTNUMBER.Value & "'") & "
ORDER BY C.CUSTOMERREFERENCE"
I've looked over all the message related to Dynamic Queries and from
what I can tell I'm doing this correctly, but for some reason it's not
working. I can remove the IIF and after and it works fine, I'm sure
its a ) or ' that I'm missing but not sure where. CONTRACTNUMBER is a
string value.
ThanksDoes this work?
SELECT C.CARDHOLDERLASTNAME,
C.CARDHOLDERFIRSTNAME,
C.CARDNUMBERLAST4DIGITS,
C.CUDISCOVERYDATE,
C.ESTIMATEDLOSSAMOUNT_QUANTITY LOSSAMOUNT,
C.CUSTOMERREFERENCE,
(SELECT Z.MYLONGLABEL
FROM APX.AENUMTYPETABLE X,
APX.AENUMTYPETABLE_MYENTRIES Y,
APX.AENUMTYPETABLEENTRY Z
WHERE X.REPORTINGTYPE = 'FraudTypeGrouping1'
AND X.NSID = Y.LONSID
AND X.ID = Y.LOID
AND Y.NSID = Z.NSID
AND Y.ID = Z.ID
AND Z.MYCODE = CHAR(C.FRAUDTYPE)) AS FRAUDTYPE,
(SELECT Z.MYLONGLABEL
FROM APX.AENUMTYPETABLE X,
APX.AENUMTYPETABLE_MYENTRIES Y,
APX.AENUMTYPETABLEENTRY Z
WHERE X.REPORTINGTYPE = 'TransactionType'
AND X.NSID = Y.LONSID
AND X.ID = Y.LOID
AND Y.NSID = Z.NSID
AND Y.ID = Z.ID
AND Z.MYCODE = CHAR(C.TRANSACTIONTYPE)) AS TRANSACTIONTYPE,
(SELECT Z.MYLONGLABEL
FROM APX.AENUMTYPETABLE X,
APX.AENUMTYPETABLE_MYENTRIES Y,
APX.AENUMTYPETABLEENTRY Z
WHERE X.REPORTINGTYPE = 'BankCardProgram'
AND X.NSID = Y.LONSID
AND X.ID = Y.LOID
AND Y.NSID = Z.NSID
AND Y.ID = Z.ID
AND Z.MYCODE = CHAR(C.CARDPROGRAM)) AS CARDPROGRAM,
'ALL' AS ALL_TEST
FROM APX.ACUWEBDATAENTRYPLASTICCARD AS C,
APX.AICPERLEGALSTRUCTURE AS D
WHERE C.CUSTOMERREFERENCE = D.CUSTOMERREFERENCE
" &
IIF(Parameters!CONTRACTNUMBER.Value = 'ALL', ", "AND
C.CUSTOMERREFERENCE = '" & Parameters!CONTRACTNUMBER.Value & "'") & "
ORDER BY C.CUSTOMERREFERENCE
formatting thanks to
http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl
I don't think it will, I think the comma in the IIF before the AND is the
problem.
Steve MunLeeuw
<kelkoenig@.gmail.com> wrote in message
news:1139434326.076692.110810@.g43g2000cwa.googlegroups.com...
> I'm using the following SQL and receiving: "The expression for the
> query 'NOL' contains an error: [BC30201] Expression expected."
> ="SELECT C.CardholderLastName, C.CardholderFirstName,
> C.CardNumberLast4Digits, C.CUDiscoveryDate,
> C.EstimatedLossAmount_Quantity LossAmount, C.CUSTOMERREFERENCE,
> (SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
> APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
> X.REPORTINGTYPE = 'FraudTypeGrouping1' AND X.NSID = Y.LONSID AND X.ID => Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE => CHAR(C.FRAUDTYPE)) as FRAUDTYPE,
> (SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
> APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
> X.REPORTINGTYPE = 'TransactionType' AND X.NSID = Y.LONSID AND X.ID => Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE => CHAR(C.TRANSACTIONTYPE)) as TRANSACTIONTYPE,
> (SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
> APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
> X.REPORTINGTYPE = 'BankCardProgram' AND X.NSID = Y.LONSID AND X.ID => Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE => CHAR(C.CARDPROGRAM)) as CARDPROGRAM, 'ALL' as ALL_TEST
> FROM APX.ACUWEBDATAENTRYPLASTICCARD AS C, APX.AICPERLEGALSTRUCTURE AS D
> WHERE C.CUSTOMERREFERENCE = D.CUSTOMERREFERENCE " &
> IIF(Parameters!CONTRACTNUMBER.Value = 'ALL', "", "AND
> C.CUSTOMERREFERENCE = '" & Parameters!CONTRACTNUMBER.Value & "'") & "
> ORDER BY C.CUSTOMERREFERENCE"
> I've looked over all the message related to Dynamic Queries and from
> what I can tell I'm doing this correctly, but for some reason it's not
> working. I can remove the IIF and after and it works fine, I'm sure
> its a ) or ' that I'm missing but not sure where. CONTRACTNUMBER is a
> string value.
> Thanks
>|||Nope, no luck, still same error w/in Reporting Services. I had to add
the = and "s at the beginning and end of the statement, unless you left
those out intentionally?
THanks|||Try enclosing the word ALL in double quotes instead of single in your
original. SRS doesn't like single quotes at all!
kelkoenig@.gmail.com wrote:
> Nope, no luck, still same error w/in Reporting Services. I had to add
> the = and "s at the beginning and end of the statement, unless you left
> those out intentionally?
> THanks|||Thanks a ton Toolman, that worked like a charm.
kelsql

Dynamic Decimal Format

I have a number: 3320.8000000. My expected result is 3320.80 based on a
dynamic decimal parameter for example:
declare idecimal int
select @.idecimal=3
convert(decimal(20,@.idecimal),number) does not work - error message.
How can I accomplish this?Format the value client side. Why would you want to do this in the database?
David Portas
SQL Server MVP
--|||I have a stored that procedure generates data for a report. The data is
stored in the database as decimal (20,7). Are you saying there is no easy wa
y
to do this using T-SQL?
"David Portas" wrote:

> Format the value client side. Why would you want to do this in the databas
e?
> --
> David Portas
> SQL Server MVP
> --
>
>|||Boy, you were one of the kids asleep in the back of my 20+ years of
database classes!! Where do we do display in a tiered architecture
(this is faaaar more fundamental question than SQL)? The front end !!
Never,never in the database!!
SQL is a static data type language. THIS IS A FUNDAMENTAL PROGRAMMING
CONCEPT!! Why did you think you could change data types in a schema?
You so ignorant or stupid that you are dangerous to people.|||Ultimately the display format is controlled by the client application, not
by SQL Server. A query always has fixed metadata and that includes the
precision and scale of each column. I think your options therefore are to
use dynamic SQL or to cast the value as a string and return it that way. You
can use the STR function to output a string with a varying numberof
decimals:
SELECT STR(x,20,@.d)
FROM T1
However, this might cause you some problems if your reporting application
needs to do arithmetic on the results.
David Portas
SQL Server MVP
--