Here is a dynamic query:
<CFSET FIRSTREC=0>
<CFSET SQL="SELECT ">
<CFLOOP QUERY="GetHeaders">
<CFIF FindNoCase(FIELDNAME1, Form.Display, 1) NEQ 0 OR OPTIONAL IS 0>
<CFIF FIRSTREC IS 1>
<CFSET SQL=SQL & ", ">
</CFIF>
<CFSET SQL=SQL & FIELDNAME1>
</CFIF>
<CFSET FIRSTREC=1>
</CFLOOP>
<CFSET SQL=SQL & " FROM PI.PI WHERE #CLIENT.CATEGORY# ">
<CFIF GetCatType.FIELDTYPE1 IS "CHAR" AND GetCatType.PARTIALKEY IS 1>
<CFSET SQL=SQL & "LIKE '%" & Ucase(Trim(CLIENT.CATVAL1)) & "%'">
<CFELSEIF GetCatType.FIELDTYPE1 IS "CHAR" AND GetCatType.PARTIALKEY IS 0>
<CFSET SQL=SQL & "= '" & Ucase(Trim(CLIENT.CATVAL1)) & "'">
<CFELSEIF GetCatType.FIELDTYPE1 IS "NUM">
<CFSET SQL=SQL & "= " & Trim(CLIENT.CATVAL1)>
</CFIF>
If I print the variable, this is what I get:
SELECT APPLYTO, ACCT, SERV, SERVNAME, BMVOL, BMREV, BMPRICE, UATVOL, UATREV, UATPRICE,
(UATVOL - BMVOL) AS "NETVOL", (UATVOL - BMVOL)/BMVOL AS
"PERVARVOL", (UATREV - BMREV) AS "NETREV", (UATREV - BMREV)/BMREV AS "PERVARREV",
(UATPRICE - BMPRICE) AS "NETPRICE", (UATPRICE - BMPRICE)/BMPRICE AS
"PERVARPRICE" FROM PI.PI WHERE MAINACCTNAME LIKE '%BILL BLASS%'
which is great. However, here is what Oracle interprets this as:
Oracle Error Code = 911
ORA-00911: invalid character
SQL = "SELECT APPLYTO, ACCT, SERV, SERVNAME, BMVOL, BMREV, BMPRICE, UATVOL, UATREV,
UATPRICE, (UATVOL - BMVOL) AS "NETVOL", (UATVOL - BMVOL)/BMVOL AS
"PERVARVOL", (UATREV - BMREV) AS "NETREV", (UATREV - BMREV)/BMREV AS "PERVARREV",
(UATPRICE - BMPRICE) AS "NETPRICE", (UATPRICE - BMPRICE)/BMPRICE AS
"PERVARPRICE" FROM PI.PI WHERE MAINACCTNAME LIKE ''%BILL BLASS%''"
Basically, it is turning the single quotes around the LIKE '%Var%' into double quotes.
Any suggestions?
Gina Shillitani
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
