|
|
On Jan 28, 9:38 pm, "AWFrueh1808" <webforumsu...@xxxxxxxxxxxxxx>
wrote:
> 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
You could simplify your query adding another clause to your where
selection as "AND formNum IN (listOfYourValuesHere)"
|
|