macromedia.coldfusion.database_access
[Top] [All Lists]

MS SQL - Syntax error for valid query

Subject: MS SQL - Syntax error for valid query
From: "AWFrueh1808" <webforumsuser@xxxxxxxxxxxxxx>
Date: Mon, 28 Jan 2008 21:38:30 +0000 (UTC)
Newsgroups: macromedia.coldfusion.database_access

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 



<Prev in Thread] Current Thread [Next in Thread>