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"
> >> >>
> >> >>
> >>
> >>
> >>
>
>
Showing posts with label receiving. Show all posts
Showing posts with label receiving. Show all posts
Tuesday, March 27, 2012
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
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
Monday, March 19, 2012
Dynamic Link server
I am receiving link server name as a parameter to my stored procedure. The code looks like
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from openquery (@.ServerName,'select column1 from table2 where column2=1')) as b on table1.item = b.column1 where qty > 0 )
begin
print 'IF Body'
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there any alternative method please suggest me
Thanks
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 from table2 where column2=1')
IF EXISTS (SELECT * FROM table1 JOIN #tbl ON ........ WHERE......)
BEGIN
END
"RajeshA" <rajeshaz09@.hotmail.com> wrote in message news:AACB61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link server name as a parameter to my stored procedure. The code looks like
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from openquery (@.ServerName,'select column1 from table2 where column2=1')) as b on table1.item = b.column1 where qty > 0 )
begin
print 'IF Body'
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there any alternative method please suggest me
Thanks
|||The problem here is, it is not accepting linked server name in the variable.
DECLARE @.Server as varchar(30)
SET @.Server = 'MyServer'
select * from openquery ( @.Server , 'select * from table1')
is not working.
select * from openquery ( MyServer , 'Select * from table1')
Is working fine.
Rajesh A
+91-9886124372
S7 Software Solutions
"NOTHING IS IMPOSSIBLE. IMPOSSIBLE IT SELF CONTAIN POSSIBLE."
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23BWvov6dIHA.484@.TK2MSFTNGP06.phx.gbl...
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 from table2 where column2=1')
IF EXISTS (SELECT * FROM table1 JOIN #tbl ON ........ WHERE......)
BEGIN
END
"RajeshA" <rajeshaz09@.hotmail.com> wrote in message news:AACB61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link server name as a parameter to my stored procedure. The code looks like
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from openquery (@.ServerName,'select column1 from table2 where column2=1')) as b on table1.item = b.column1 where qty > 0 )
begin
print 'IF Body'
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there any alternative method please suggest me
Thanks
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from openquery (@.ServerName,'select column1 from table2 where column2=1')) as b on table1.item = b.column1 where qty > 0 )
begin
print 'IF Body'
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there any alternative method please suggest me
Thanks
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 from table2 where column2=1')
IF EXISTS (SELECT * FROM table1 JOIN #tbl ON ........ WHERE......)
BEGIN
END
"RajeshA" <rajeshaz09@.hotmail.com> wrote in message news:AACB61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link server name as a parameter to my stored procedure. The code looks like
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from openquery (@.ServerName,'select column1 from table2 where column2=1')) as b on table1.item = b.column1 where qty > 0 )
begin
print 'IF Body'
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there any alternative method please suggest me
Thanks
|||The problem here is, it is not accepting linked server name in the variable.
DECLARE @.Server as varchar(30)
SET @.Server = 'MyServer'
select * from openquery ( @.Server , 'select * from table1')
is not working.
select * from openquery ( MyServer , 'Select * from table1')
Is working fine.
Rajesh A
+91-9886124372
S7 Software Solutions
"NOTHING IS IMPOSSIBLE. IMPOSSIBLE IT SELF CONTAIN POSSIBLE."
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23BWvov6dIHA.484@.TK2MSFTNGP06.phx.gbl...
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 from table2 where column2=1')
IF EXISTS (SELECT * FROM table1 JOIN #tbl ON ........ WHERE......)
BEGIN
END
"RajeshA" <rajeshaz09@.hotmail.com> wrote in message news:AACB61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link server name as a parameter to my stored procedure. The code looks like
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from openquery (@.ServerName,'select column1 from table2 where column2=1')) as b on table1.item = b.column1 where qty > 0 )
begin
print 'IF Body'
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there any alternative method please suggest me
Thanks
Sunday, March 11, 2012
Dynamic File location for DTS transfer
Does anyone know if this is possible:
I have an FTP server that will be receiving files. The directoryand file structure will be a folder with a client name (can be calledanything) and it will have files in it (these files will have the samefilenames as all the other directories. So I will have folderJimmyDoe with files a.txt, b.txt, c.txt and I will have JonnyDue withfiles a.txt, b.txt, and c.txt.
Now I'm trying to figure out a way to get that dynamic file location toa DTS package so I can import all the data from the text file into aSQL server. The way the SQL server will be set up is that eachFolder from the FTP site will be a separate Database and each file will1:1 with a table with the same name..
My biggest issue is figuring out a way to tell the DTS package the filelocation to pull all those files and then importing them to the properdatabase.
I'm not limiting the solution to DTS packages so if .NET can beincorporated to make it easier then so be it. But keep in mind Ican have up to 200 folders with 12 - 20 text files ranging fromhundreds of rows of data to many thousands of rows. And thepackage needs to be ran twice a day so time/performance is anissue.
To recap: Need DTS package that uses Dynamic file source and transfers data to Dynamic database destination.
(And I'll write slow VB.NET code to handle this before I create/manage 200+ DTS packages as a solution)
Any help at all is greatly appreciated.
How are you executing the package? If you execute dynamically, such as through the dtsrun utility or through SQLDMO code, you should be able to pass a value into a global variable. Then use the dynamic properties task to change the default global variable value to your new value.|||I've used DTS Run before so that's the only way I know how to dothat. How would I use the dynamic properties task to change thedefault global variable value? Can you give me a snippet,pseuodcode or something of how that would work?
Never heard of SQLDMO, what is that?
|||
I have an FTP server that will be receiving files. The directoryand file structure will be a folder with a client name (can be calledanything) and it will have files in it (these files will have the samefilenames as all the other directories. So I will have folderJimmyDoe with files a.txt, b.txt, c.txt and I will have JonnyDue withfiles a.txt, b.txt, and c.txt.
Now I'm trying to figure out a way to get that dynamic file location toa DTS package so I can import all the data from the text file into aSQL server. The way the SQL server will be set up is that eachFolder from the FTP site will be a separate Database and each file will1:1 with a table with the same name..
My biggest issue is figuring out a way to tell the DTS package the filelocation to pull all those files and then importing them to the properdatabase.
I'm not limiting the solution to DTS packages so if .NET can beincorporated to make it easier then so be it. But keep in mind Ican have up to 200 folders with 12 - 20 text files ranging fromhundreds of rows of data to many thousands of rows. And thepackage needs to be ran twice a day so time/performance is anissue.
To recap: Need DTS package that uses Dynamic file source and transfers data to Dynamic database destination.
(And I'll write slow VB.NET code to handle this before I create/manage 200+ DTS packages as a solution)
Any help at all is greatly appreciated.
How are you executing the package? If you execute dynamically, such as through the dtsrun utility or through SQLDMO code, you should be able to pass a value into a global variable. Then use the dynamic properties task to change the default global variable value to your new value.|||I've used DTS Run before so that's the only way I know how to dothat. How would I use the dynamic properties task to change thedefault global variable value? Can you give me a snippet,pseuodcode or something of how that would work?
Never heard of SQLDMO, what is that?
|||
netflash99 wrote:
Never heard of SQLDMO, what is that?
SQLDMO(SQL Server Data Management Object) Microsoft property it creates everything you do with Enterprise Manager manually through code but it uses System tables from the master to do its work so your code will be orphaned in SQL Server 2005 where those tables are really Microsoft Property you because cannot use them. DTS Run is good practice. Try the url below on using Global Variable. Hope this helps.
http://www.sqldts.com
Subscribe to:
Comments (Atom)