Rich,
Here are a couple of examples of how I create dynamic where clauses:
<CFQuery Name="GetThisEmployee" DataSource="#systemds#">
SELECT #MySelectList#
FROM #MyStafPakPath#\NurseP n
WHERE Active = 'Y'
<CFIf Len(MyNurseSSN)>
AND (0=1
OR AllTrim(n.SSAN) = '#MyNurseSSN#'
OR n.EmpNo = '#MyNurseSSN#'
)
</CFIf>
<CFIf Len(MyNurseFirstName)>
AND n.FName = '#MyNurseFirstName#'
</CFIf>
<CFIf Len(MyNurseLastName)>
AND n.LName = '#MyNurseLastName#'
</CFIf>
</CFQuery>
or:
<CFQuery name="My.SearchForNurseQuery" datasource="#SystemDS#">
SELECT #My.SelectList#
FROM #My.MarketPath#\NurseP
WHERE 1=1
AND UCase(Active) = 'Y'
<CFLoop Index="i" From="1" To="#ListLen(My.Keyword, ' ')#">
<CFSet My.IndividualKeyword = UCase(ListGetAt(My.Keyword, i,
" "))>
AND (0=1
OR UCase(SSAN) LIKE <CFQueryParam
Value="%#My.IndividualKeyword#%" CFSQLType="CF_SQL_VARCHAR">
OR UCase(EMPNO) LIKE <CFQueryParam
Value="%#My.IndividualKeyword#%" CFSQLType="CF_SQL_VARCHAR">
OR UCase(LName) LIKE <CFQueryParam
Value="%#My.IndividualKeyword#%" CFSQLType="CF_SQL_VARCHAR">
OR UCase(FName) LIKE <CFQueryParam
Value="%#My.IndividualKeyword#%" CFSQLType="CF_SQL_VARCHAR">
OR UCase(MName) LIKE <CFQueryParam
Value="%#My.IndividualKeyword#%" CFSQLType="CF_SQL_VARCHAR">
)
</CFLoop>
ORDER BY LName, FName
</CFQuery>
I hope that last one is readable, it wraps on my screen. :o'
Anyway, I hope this helps!
Cheers,
Chris
RichL wrote:
> Guys
>
> I am trying to build up a where clause dynamically as follows:
>
> <cfset whereClause = 'Where 1 = 0 or '>
>
> loop with i as a numeric index value...
>
> <cfset whereClause = whereClause & '(centreid = <cfqueryparam
> cfsqltype="cf_sql_numeric" value="#i#"> and cplname = <cfqueryparam
> cfsqltype="cf_sql_varchar" value="#attributes["cplname_#i#"]#">)'>
>
> end loop
>
> I am running a query as follows: select * from x #whereclause#
>
> The SQL is falling over with 'Line 1: Incorrect syntax near '<'.' but
> on the same error message it outputs the SQL being run as:
>
> select * from rik_viewCPLReferenceData Where 1 = 0 or (centreid =
> <cfqueryparam cfsqltype="cf_sql_numeric" value="78"> and cplname =
> <cfqueryparam cfsqltype="cf_sql_varchar" value="BSc Accounting for
> Management">)
>
> which is exactly what I want and if I copy and paste this and run it
> directly, all is well.
>
> my suspicion is that this SQL being shown on the screen is correct
> after it has been rendered to screen but not at the time CF picks it
> up and passes it to SQL Server?
>
> I have messed about with htmleditformat, htmlcodeformat, urldecode,
> urlencodedformat, de, evaluate, using '<' '>' both within the
> initial where clause creation and the insertion of the #whereClause#
> variable in the query and can't get anything to work.
>
> Can anybody shed some light on this or suggest a better way to do it please?
>
> Many thanks
>
>
--
http://www.cjordan.info
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:264717
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
