Copy link to clipboard
Copied
Hi All,
I have a field value that can be stored with many values for a single record: Work_Order = '555555,666666,111111' (note, these are actually stored as a single string value in the db with commas .)
I need to parse this field and enter the string values into the where clause of a new query:
<CFSET WOIDS = CapProjects.Work_Order>
<cfloop condition="#find(',,',WOIDS)#">
<cfset WOIDS = "#rereplace(WOIDS,',,',',null,','ALL')#"><br>
</cfloop>
<cfquery name="CapProjectsWOIDS" datasource="#URL.DB#">
SELECT [ENGOPSMTRLSID]
,[Work_Order]
FROM ENGOPSMTRLS
WHERE
<CFOUTPUT>
ENGOPSMTRLSID = #URL.INDEX#
</CFOUTPUT>
AND
<CFOUTPUT>
Work_Order in (<cfloop index="idx" list="#variables.WOIDS#">
<cfqueryparam
value=#WOIDS#
cfsqltype="cf_sql_varchar"
list="true"
/></cfloop>)
</CFOUTPUT>
</cfquery>
Am I doing this right? I'm getting the following error: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '@P4'. Is that the loop length? How do I pass that in?
Thanks,
John
First, you don't need to use CFOUTPUT within a CFQUERY (or most/all other CFtags) - CF knows.
Second, you don't need to loop a list for an "IN". Unless you're using some obscure database that isn't MS-SQL, MySQL, or Oracle. And you already know that CFQUERYPARAM has a list attribute.
NOTE: This is assuming that the datatype of Work_Order is char or varchar, not integer.
...<cfquery name="CapProjectsWOIDS" datasource="#URL.DB#">
SELECT [ENGOPSMTRLSID]
,[Work_Order]
FROM ENGOPSMTRLS
Copy link to clipboard
Copied
First, you don't need to use CFOUTPUT within a CFQUERY (or most/all other CFtags) - CF knows.
Second, you don't need to loop a list for an "IN". Unless you're using some obscure database that isn't MS-SQL, MySQL, or Oracle. And you already know that CFQUERYPARAM has a list attribute.
NOTE: This is assuming that the datatype of Work_Order is char or varchar, not integer.
<cfquery name="CapProjectsWOIDS" datasource="#URL.DB#">
SELECT [ENGOPSMTRLSID]
,[Work_Order]
FROM ENGOPSMTRLS
WHERE ENGOPSMTRLSID = #URL.INDEX# /* <--- I didn't see URL anything, before this. And I REALLY cannot stress how bad of an idea it is to use a URL variable for your DSN. Just sayin'. */
AND
Work_Order in (<cfqueryparam
value="#WOIDS#"
cfsqltype="cf_sql_varchar"
list="true"/>)
</cfquery>
Since you are using a varchar for the type, you should put double-quotes around the value (line 08), as I have demonstrated.
HTH,
^_^
Copy link to clipboard
Copied
ahhh! double quotes! Thanks