|
|
I have a problem with the second query in this code (gives me a syntax error in
my HAVING line)
<cfquery name="getDupes" datasource="#application.ODBC1#" dbtype="ODBC"
username="#application.userNameODBC1#" password="#application.userPassODBC1#">
SELECT *
FROM view_FormCount
WHERE formCount >= 2
ORDER BY formNum
</cfquery>
<cfset variables.lstForms = "">
<cfoutput query="getDupes">
<cfset variables.lstForms=
listAppend(variables.lstForms,"'#getDupes.formNum#'")>
</cfoutput>
<cfset variables.whereClause = "(formNum =
#ReplaceNoCase(variables.lstForms,',',' OR formNum = ','ALL')#)">
<cfquery name="getDetails" datasource="#application.ODBC1#" dbtype="ODBC"
username="#application.userNameODBC1#" password="#application.userPassODBC1#">
SELECT tblForms.formNum, tblForms.formName, tblForms.formCustNum,
tblCompanies.companyAbbr
FROM tblForms INNER JOIN tblCompanies ON tblForms.formCustNum =
tblCompanies.companyNum
GROUP BY tblForms.formNum, tblForms.formName, tblForms.formCustNum,
tblCompanies.companyAbbr
HAVING #variables.whereClause#
ORDER BY tblForms.formNum, tblForms.formCustNum
</cfquery>
Now, the really odd thing is if I just take the query text out of the CFQUERY
tags and use CFOUTPUT instead (to see in my browser what query is getting
executed) I get a query that I can paste into Enterprise Mgr and runs fine.
I can even paste the resulting text into my code in a CFQUERY and CF will run
it fine.
In other words, the dynamic version query gives me an error, but if I just
output the SQL statement, copy-and-paste that into the CFQUERY that's erroring
out (replacing what's there) to run as a static query, it works fine (sample of
static query below).
The error I get when I try to do it dynamically is "Error Executing Database
Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Line 4: Incorrect syntax
near '10169318'. The error occurred on line 21." which is where the HAVING
clause starts.
SELECT tblForms.formNum, tblForms.formName, tblForms.formCustNum,
tblCompanies.companyAbbr FROM tblForms INNER JOIN tblCompanies ON
tblForms.formCustNum = tblCompanies.companyNum GROUP BY tblForms.formNum,
tblForms.formName, tblForms.formCustNum, tblCompanies.companyAbbr HAVING
(formNum = '10169318' OR formNum = '1016CONCERTA' OR formNum = '1016NSAIDS' OR
formNum = '1016STRATTERA' OR formNum = '1016WELLBUTRIN' OR formNum = '18504211'
OR formNum = '185093807' OR formNum = '73208565' OR formNum = '732120027' OR
formNum = '7322154' OR formNum = '7323402' OR formNum = '7323522' OR formNum =
'73238900' OR formNum = '7324211' OR formNum = '7324211S' OR formNum =
'7324265' OR formNum = '73242891' OR formNum = '732434341' OR formNum =
'732434342' OR formNum = '73243435' OR formNum = '73243436' OR formNum =
'73243439' OR formNum = '73243440' OR formNum = '73243441' OR formNum =
'73243442' OR formNum = '73243443' OR formNum = '7324359' OR formNum =
'7324360' OR formNum = '7324370' OR formNum = '7324560' OR formNum = '7324959'
OR formNum = '7326411ptreg' OR formNum = '7326599' OR formNum = '7326600' OR
formNum = '7326620' OR formNum = '7326707' OR formNum = '7326931' OR formNum =
'7328410' OR formNum = '732851031TOP' OR formNum = '7328512' OR formNum =
'7328548' OR formNum = '7328548P2' OR formNum = '7328548P3' OR formNum =
'7328548P4' OR formNum = '73289400' OR formNum = '73290D' OR formNum =
'7329154' OR formNum = '73291544JHS' OR formNum = '73291545JHS' OR formNum =
'73291546JHSMH' OR formNum = '73291547JHSMH' OR formNum = '7329174' OR formNum
= '7329308' OR formNum = '7329402' OR formNum = '7329424' OR formNum =
'7329455' OR formNum = '7329520' OR formNum = '7329539' OR formNum =
'73297701JH' OR formNum = '73298273' OR formNum = '73298400' OR formNum =
'73298403' OR formNum = '73298404' OR formNum = '7329883' OR formNum =
'73298860' OR formNum = '7329887' OR formNum = '73298974' OR formNum =
'7329899' OR formNum = '7329899S' OR formNum = '73299190' OR formNum =
'7329987' OR formNum = '7329999PTO' OR formNum = '732AV5160' OR formNum =
'732CMHFRAZ' OR formNum = '732HIPAA' OR formNum = '732HIPAAFLYERS' OR formNum =
'734HEART' OR formNum = '7444711' OR formNum = '7449230' OR formNum =
'744HR4991' OR formNum = '7538014MI' OR formNum = '77511044' OR formNum =
'77511045' OR formNum = '775stampnochange' OR formNum = '80851001BLUE' OR
formNum = '80MR4200' OR formNum = '80MR4273' OR formNum = '80MR4274' OR formNum
= '80MR4300' OR formNum = '80MR9934' OR formNum = '80MR9935' OR formNum =
'80SHC100' OR formNum = '80SHC4301' OR formNum = '80SHC9830' OR formNum =
'80SHC9832') ORDER BY tblForms.formNum, tblForms.formCustNum
|
|