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