Re: Correct Syntax for this piece of SQL ??
Ian, Replace WHERE 0 = 1 in your current query with either WHERE 0 = 0 or WHERE 1 = 1. Why? This is just a dummy clause that's used in a dynamic SQL statement to let's you move past the WHERE and on to the dynamic AND/OR statements that are based on your conditions. But this dummy WHERE clause must be true, or processing will stop right there. Obviously, WHERE 0 = 1 is *not* true. Fix that and you should be OK. ~Dina - Original Message - From: Ian Vaughan [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 9:17 AM Subject: Re: Correct Syntax for this piece of SQL ?? Pascal I removed the cached feature so the query looks like what I have below. However If I search for an entry in the orgname field it displays the result as it should. If I try to search in the other two fields it returns no results ??? CFQUERY datasource=liv8 name=funding SELECT * FROM funding WHERE 0=1 cfif Len(Trim(form.orgname)) OR UPPER(orgname) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.orgname#%) /cfif cfif Len(Trim(form.funding)) OR UPPER(funding) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.funding#%) /cfif cfif Len(Trim(form.commapproval)) OR UPPER(commapproval) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.commapproval#%) /cfif /CFQUERY - Original Message - From: Pascal Peters [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 2:49 PM Subject: RE: Correct Syntax for this piece of SQL ?? You cannot use cfqueryparam with cached queries. Go back to UPPER('%#Trim(form.var)#%') -Oorspronkelijk bericht- Van: Ian Vaughan [mailto:[EMAIL PROTECTED] Verzonden: wo 26/02/2003 14:17 Aan: CF-Talk CC: Onderwerp: Re: Correct Syntax for this piece of SQL ?? Pascal Here is my query CFQUERY datasource=liv8 name=funding cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100 SELECT * FROM funding WHERE 0=1 cfif Len(Trim(form.orgname)) OR UPPER(orgname) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.orgname#%) /cfif cfif Len(Trim(form.funding)) OR UPPER(funding) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.funding#%) /cfif cfif Len(Trim(form.commapproval)) OR UPPER(commapproval) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.commapproval#%) /cfif /CFQUERY CFIF funding.RecordCount is 0 span class=black10pbNo files found for specified criteria/b/p !--- ... else at least one file found --- CFELSE cfif sgn(Evaluate(funding.RecordCount - Form.MaxRows - Form.StartRow)) -1 p CFOUTPUT smallspan class=black10Viewing #Form.StartRow# to #funding.RecordCount# of b#funding.RecordCount#/b records found./font/small/span /cfoutput /cfif div align=left table width=761 border=0 tbody tr th background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif; align=leftspan class=black10Organization Name/th th background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif; align=leftspan class=black10Funding/th th background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif; align=leftspan class=black10Committe Approval/th /tr cfoutput query=funding startrow=#StartRow# maxrows=#Form.MaxRows# tr BGCOLOR=###IIF(funding.currentrow MOD 2, DE ('e7efef'), DE ('f7f7de'))# !--- td align=left valign=top background=http://intranet.neath-porttalbot.gov.uk/images/tablebg1.gif;sp an class=black10#Evaluate(CurrentRow)#/td --- td align=left valign=top span class=black10#orgname#/td td align=left valign=topspan class=black10#funding#/td td align=left valign=top span class=black10#commapproval#/td /tr /cfoutput/tbody/table - Original Message - From: Pascal Peters [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 11:34 AM Subject: RE: Correct Syntax for this piece of SQL ?? This should work on ORACLE8/CF4.5.1 (I'm developping for that platform right now) I can't make much of your error. Can you post your entire cfquery. CFQUERYPARAM is used for parameterized sql. It is usually good for performence and it helps to avoid sql hacks. cfsqltype describes the datatype of your column (in this case a VARCHAR2). You can check out the help or devnet (http://www.macromedia.com/desdev/articles/ben_forta_faster.html) -Oorspronkelijk bericht- Van: Ian Vaughan [mailto:[EMAIL PROTECTED] Verzonden: wo 26/02/2003 11:55 Aan: CF-Talk CC: Onderwerp: Re: Correct Syntax for this piece of SQL ?? Pascal Thanks for your solution but it does not work, I am using CF 4.5 and Oracle 8. I am getting the following error when using your code, is it not compatible
Re: Correct Syntax for this piece of SQL ??
Hi I have just tried the following query select * from funding Where ( orgname LIKE '%#Form.orgname#%' ) OR( funding LIKE '%#Form.funding#%' ) OR( commapproval LIKE '%#Form.commapproval#%' ) ORDER BY recordid however whatever I type in the fields it returns all results ??? All 3 criteria do no have to be met, Any ideas why the above sql is returning all results ?? Ian - Original Message - From: Jann VanOver [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 5:48 AM Subject: Re: Correct Syntax for this piece of SQL ?? I think Lee said it too, you've got a basic logic flaw. Your query says all three criteria must be met. Did you mean to say: select * from funding Where ( orgname LIKE '%#Form.orgname#%' ) OR( funding LIKE '%#Form.funding#%' ) OR( commapproval LIKE '%#Form.commapproval#%' ) ORDER BY recordid You can use parenthesis to be MORE subtle, like if you want the second and third criteria to be required together: Where ( orgname LIKE '%#Form.orgname#%' ) OR ( ( funding LIKE '%#Form.funding#%' ) AND ( commapproval LIKE '%#Form.commapproval#%' ) ) On 2/25/03 6:48 AM, Ian Vaughan [EMAIL PROTECTED] wrote: Hi I have the following form which is being used to search against my database table. When I search using the 'orgname' field and there are matches then results are returned. However if I search in the other two fields then it displays no results found when it should bring back results ? Is this because of my sql ?? CFQUERY datasource=liv8 name=funding cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100 select * from funding Where orgname LIKE UPPER('%#Form.orgname#%') AND funding LIKE UPPER('%#Form.funding#%') AND commapproval LIKE UPPER('%#Form.commapproval#%') ORDER BY recordid /CFQUERY This is my search form form method=POST action=/testpages/fundingresults.cfm name=search input type=Hidden name=MaxRows value=100 input type=hidden name=StartRow value=1 font color=#00 face=Verdanaspan class=black10 p input type=text class=mini name=orgname size=22 style=WIDTH: 200pxb Organization Name/b /p input type=text class=mini name=funding size=22 style=WIDTH: 200pxbSource of Funding/b p input type=text class=mini name=commapproval size=22 style=WIDTH: 200pxbCommittee Approval Date/b /p p input type=submit value=Search name=B1nbsp;nbsp; input type=reset value=Reset Form name=B2 p /form ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Correct Syntax for this piece of SQL ??
If one of your form variables is empty, it will always return all the records because you match '%%' (which means anything). If you want to match one of the criteria you entered, this should work: SELECT * FROM funding WHERE 0=1 cfif Len(Trim(form.orgname)) OR UPPER(orgname) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.orgname#%) /cfif cfif Len(Trim(form.funding)) OR UPPER(funding) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.funding#%) /cfif cfif Len(Trim(form.commapproval)) OR UPPER(commapproval) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.commapproval#%) /cfif This will have no result if you don't enter a value at all. If you want to match all if you don't provide any value, you can add a cfif around the entire where clause cfif Len(Trim(form.orgnameform.fundingform.commapproval)) -Oorspronkelijk bericht- Van: Ian Vaughan [mailto:[EMAIL PROTECTED] Verzonden: wo 26/02/2003 10:20 Aan: CF-Talk CC: Onderwerp: Re: Correct Syntax for this piece of SQL ?? Hi I have just tried the following query select * from funding Where ( orgname LIKE '%#Form.orgname#%' ) OR( funding LIKE '%#Form.funding#%' ) OR( commapproval LIKE '%#Form.commapproval#%' ) ORDER BY recordid however whatever I type in the fields it returns all results ??? All 3 criteria do no have to be met, Any ideas why the above sql is returning all results ?? Ian ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Correct Syntax for this piece of SQL ??
Pascal Thanks for your solution but it does not work, I am using CF 4.5 and Oracle 8. I am getting the following error when using your code, is it not compatible with 4.5 ? and what is the need for cfqueryparam cfsqltype=CF_SQL_VARCHAR ?? Error Occurred While Processing Request Error Diagnostic Information CFQuery The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (61:1) to (62:59 - Original Message - From: Pascal Peters [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 10:12 AM Subject: RE: Correct Syntax for this piece of SQL ?? If one of your form variables is empty, it will always return all the records because you match '%%' (which means anything). If you want to match one of the criteria you entered, this should work: SELECT * FROM funding WHERE 0=1 cfif Len(Trim(form.orgname)) OR UPPER(orgname) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.orgname#%) /cfif cfif Len(Trim(form.funding)) OR UPPER(funding) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.funding#%) /cfif cfif Len(Trim(form.commapproval)) OR UPPER(commapproval) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.commapproval#%) /cfif This will have no result if you don't enter a value at all. If you want to match all if you don't provide any value, you can add a cfif around the entire where clause cfif Len(Trim(form.orgnameform.fundingform.commapproval)) -Oorspronkelijk bericht- Van: Ian Vaughan [mailto:[EMAIL PROTECTED] Verzonden: wo 26/02/2003 10:20 Aan: CF-Talk CC: Onderwerp: Re: Correct Syntax for this piece of SQL ?? Hi I have just tried the following query select * from funding Where ( orgname LIKE '%#Form.orgname#%' ) OR( funding LIKE '%#Form.funding#%' ) OR( commapproval LIKE '%#Form.commapproval#%' ) ORDER BY recordid however whatever I type in the fields it returns all results ??? All 3 criteria do no have to be met, Any ideas why the above sql is returning all results ?? Ian ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Correct Syntax for this piece of SQL ??
This should work on ORACLE8/CF4.5.1 (I'm developping for that platform right now) I can't make much of your error. Can you post your entire cfquery. CFQUERYPARAM is used for parameterized sql. It is usually good for performence and it helps to avoid sql hacks. cfsqltype describes the datatype of your column (in this case a VARCHAR2). You can check out the help or devnet (http://www.macromedia.com/desdev/articles/ben_forta_faster.html) -Oorspronkelijk bericht- Van: Ian Vaughan [mailto:[EMAIL PROTECTED] Verzonden: wo 26/02/2003 11:55 Aan: CF-Talk CC: Onderwerp: Re: Correct Syntax for this piece of SQL ?? Pascal Thanks for your solution but it does not work, I am using CF 4.5 and Oracle 8. I am getting the following error when using your code, is it not compatible with 4.5 ? and what is the need for cfqueryparam cfsqltype=CF_SQL_VARCHAR ?? Error Occurred While Processing Request Error Diagnostic Information CFQuery The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (61:1) to (62:59 - Original Message - From: Pascal Peters [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 10:12 AM Subject: RE: Correct Syntax for this piece of SQL ?? If one of your form variables is empty, it will always return all the records because you match '%%' (which means anything). If you want to match one of the criteria you entered, this should work: SELECT * FROM funding WHERE 0=1 cfif Len(Trim(form.orgname)) OR UPPER(orgname) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.orgname#%) /cfif cfif Len(Trim(form.funding)) OR UPPER(funding) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.funding#%) /cfif cfif Len(Trim(form.commapproval)) OR UPPER(commapproval) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.commapproval#%) /cfif This will have no result if you don't enter a value at all. If you want to match all if you don't provide any value, you can add a cfif around the entire where clause cfif Len(Trim(form.orgnameform.fundingform.commapproval)) -Oorspronkelijk bericht- Van: Ian Vaughan [mailto:[EMAIL PROTECTED] Verzonden: wo 26/02/2003 10:20 Aan: CF-Talk CC: Onderwerp: Re: Correct Syntax for this piece of SQL ?? Hi I have just tried the following query select * from funding Where ( orgname LIKE '%#Form.orgname#%' ) OR( funding LIKE '%#Form.funding#%' ) OR( commapproval LIKE '%#Form.commapproval#%' ) ORDER BY recordid however whatever I type in the fields it returns all results ??? All 3 criteria do no have to be met, Any ideas why the above sql is returning all results ?? Ian ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Correct Syntax for this piece of SQL ??
Pascal Here is my query CFQUERY datasource=liv8 name=funding cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100 SELECT * FROM funding WHERE 0=1 cfif Len(Trim(form.orgname)) OR UPPER(orgname) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.orgname#%) /cfif cfif Len(Trim(form.funding)) OR UPPER(funding) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.funding#%) /cfif cfif Len(Trim(form.commapproval)) OR UPPER(commapproval) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.commapproval#%) /cfif /CFQUERY CFIF funding.RecordCount is 0 span class=black10pbNo files found for specified criteria/b/p !--- ... else at least one file found --- CFELSE cfif sgn(Evaluate(funding.RecordCount - Form.MaxRows - Form.StartRow)) -1 p CFOUTPUT smallspan class=black10Viewing #Form.StartRow# to #funding.RecordCount# of b#funding.RecordCount#/b records found./font/small/span /cfoutput /cfif div align=left table width=761 border=0 tbody tr th background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif; align=leftspan class=black10Organization Name/th th background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif; align=leftspan class=black10Funding/th th background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif; align=leftspan class=black10Committe Approval/th /tr cfoutput query=funding startrow=#StartRow# maxrows=#Form.MaxRows# tr BGCOLOR=###IIF(funding.currentrow MOD 2, DE ('e7efef'), DE ('f7f7de'))# !--- td align=left valign=top background=http://intranet.neath-porttalbot.gov.uk/images/tablebg1.gif;sp an class=black10#Evaluate(CurrentRow)#/td --- td align=left valign=top span class=black10#orgname#/td td align=left valign=topspan class=black10#funding#/td td align=left valign=top span class=black10#commapproval#/td /tr /cfoutput/tbody/table - Original Message - From: Pascal Peters [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 11:34 AM Subject: RE: Correct Syntax for this piece of SQL ?? This should work on ORACLE8/CF4.5.1 (I'm developping for that platform right now) I can't make much of your error. Can you post your entire cfquery. CFQUERYPARAM is used for parameterized sql. It is usually good for performence and it helps to avoid sql hacks. cfsqltype describes the datatype of your column (in this case a VARCHAR2). You can check out the help or devnet (http://www.macromedia.com/desdev/articles/ben_forta_faster.html) -Oorspronkelijk bericht- Van: Ian Vaughan [mailto:[EMAIL PROTECTED] Verzonden: wo 26/02/2003 11:55 Aan: CF-Talk CC: Onderwerp: Re: Correct Syntax for this piece of SQL ?? Pascal Thanks for your solution but it does not work, I am using CF 4.5 and Oracle 8. I am getting the following error when using your code, is it not compatible with 4.5 ? and what is the need for cfqueryparam cfsqltype=CF_SQL_VARCHAR ?? Error Occurred While Processing Request Error Diagnostic Information CFQuery The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (61:1) to (62:59 - Original Message - From: Pascal Peters [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 10:12 AM Subject: RE: Correct Syntax for this piece of SQL ?? If one of your form variables is empty, it will always return all the records because you match '%%' (which means anything). If you want to match one of the criteria you entered, this should work: SELECT * FROM funding WHERE 0=1 cfif Len(Trim(form.orgname)) OR UPPER(orgname) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.orgname#%) /cfif cfif Len(Trim(form.funding)) OR UPPER(funding) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.funding#%) /cfif cfif Len(Trim(form.commapproval)) OR UPPER(commapproval) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.commapproval#%) /cfif This will have no result if you don't enter a value at all. If you want to match all if you don't provide any value, you can add a cfif around the entire where clause cfif Len(Trim(form.orgnameform.fundingform.commapproval)) -Oorspronkelijk bericht- Van: Ian Vaughan [mailto:[EMAIL PROTECTED] Verzonden: wo 26/02/2003 10:20 Aan: CF-Talk CC: Onderwerp: Re: Correct Syntax for this piece of SQL ?? Hi I have just tried the following query select * from funding Where ( orgname LIKE '%#Form.orgname#%' ) OR( funding LIKE '%#Form.funding#%' ) OR( commapproval LIKE '%#Form.commapproval#%' ) ORDER BY recordid however whatever I type in the fields it returns all results ??? All 3 criteria do no have to be met, Any ideas why the above sql is returning all results ?? Ian
RE: Correct Syntax for this piece of SQL ??
You cannot use cfqueryparam with cached queries. Go back to UPPER('%#Trim(form.var)#%') -Oorspronkelijk bericht- Van: Ian Vaughan [mailto:[EMAIL PROTECTED] Verzonden: wo 26/02/2003 14:17 Aan: CF-Talk CC: Onderwerp: Re: Correct Syntax for this piece of SQL ?? Pascal Here is my query CFQUERY datasource=liv8 name=funding cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100 SELECT * FROM funding WHERE 0=1 cfif Len(Trim(form.orgname)) OR UPPER(orgname) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.orgname#%) /cfif cfif Len(Trim(form.funding)) OR UPPER(funding) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.funding#%) /cfif cfif Len(Trim(form.commapproval)) OR UPPER(commapproval) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.commapproval#%) /cfif /CFQUERY CFIF funding.RecordCount is 0 span class=black10pbNo files found for specified criteria/b/p !--- ... else at least one file found --- CFELSE cfif sgn(Evaluate(funding.RecordCount - Form.MaxRows - Form.StartRow)) -1 p CFOUTPUT smallspan class=black10Viewing #Form.StartRow# to #funding.RecordCount# of b#funding.RecordCount#/b records found./font/small/span /cfoutput /cfif div align=left table width=761 border=0 tbody tr th background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif; align=leftspan class=black10Organization Name/th th background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif; align=leftspan class=black10Funding/th th background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif; align=leftspan class=black10Committe Approval/th /tr cfoutput query=funding startrow=#StartRow# maxrows=#Form.MaxRows# tr BGCOLOR=###IIF(funding.currentrow MOD 2, DE ('e7efef'), DE ('f7f7de'))# !--- td align=left valign=top background=http://intranet.neath-porttalbot.gov.uk/images/tablebg1.gif;sp an class=black10#Evaluate(CurrentRow)#/td --- td align=left valign=top span class=black10#orgname#/td td align=left valign=topspan class=black10#funding#/td td align=left valign=top span class=black10#commapproval#/td /tr /cfoutput/tbody/table - Original Message - From: Pascal Peters [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 11:34 AM Subject: RE: Correct Syntax for this piece of SQL ?? This should work on ORACLE8/CF4.5.1 (I'm developping for that platform right now) I can't make much of your error. Can you post your entire cfquery. CFQUERYPARAM is used for parameterized sql. It is usually good for performence and it helps to avoid sql hacks. cfsqltype describes the datatype of your column (in this case a VARCHAR2). You can check out the help or devnet (http://www.macromedia.com/desdev/articles/ben_forta_faster.html) -Oorspronkelijk bericht- Van: Ian Vaughan [mailto:[EMAIL PROTECTED] Verzonden: wo 26/02/2003 11:55 Aan: CF-Talk CC: Onderwerp: Re: Correct Syntax for this piece of SQL ?? Pascal Thanks for your solution but it does not work, I am using CF 4.5 and Oracle 8. I am getting the following error when using your code, is it not compatible with 4.5 ? and what is the need for cfqueryparam cfsqltype=CF_SQL_VARCHAR ?? Error Occurred While Processing Request Error Diagnostic Information CFQuery The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (61:1) to (62:59 - Original Message - From: Pascal Peters [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 10:12 AM Subject: RE: Correct Syntax for this piece of SQL ?? If one of your form variables is empty, it will always return all the records because you match '%%' (which means anything). If you want to match
Re: Correct Syntax for this piece of SQL ??
Pascal I removed the cached feature so the query looks like what I have below. However If I search for an entry in the orgname field it displays the result as it should. If I try to search in the other two fields it returns no results ??? CFQUERY datasource=liv8 name=funding SELECT * FROM funding WHERE 0=1 cfif Len(Trim(form.orgname)) OR UPPER(orgname) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.orgname#%) /cfif cfif Len(Trim(form.funding)) OR UPPER(funding) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.funding#%) /cfif cfif Len(Trim(form.commapproval)) OR UPPER(commapproval) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.commapproval#%) /cfif /CFQUERY - Original Message - From: Pascal Peters [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 2:49 PM Subject: RE: Correct Syntax for this piece of SQL ?? You cannot use cfqueryparam with cached queries. Go back to UPPER('%#Trim(form.var)#%') -Oorspronkelijk bericht- Van: Ian Vaughan [mailto:[EMAIL PROTECTED] Verzonden: wo 26/02/2003 14:17 Aan: CF-Talk CC: Onderwerp: Re: Correct Syntax for this piece of SQL ?? Pascal Here is my query CFQUERY datasource=liv8 name=funding cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100 SELECT * FROM funding WHERE 0=1 cfif Len(Trim(form.orgname)) OR UPPER(orgname) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.orgname#%) /cfif cfif Len(Trim(form.funding)) OR UPPER(funding) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.funding#%) /cfif cfif Len(Trim(form.commapproval)) OR UPPER(commapproval) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#form.commapproval#%) /cfif /CFQUERY CFIF funding.RecordCount is 0 span class=black10pbNo files found for specified criteria/b/p !--- ... else at least one file found --- CFELSE cfif sgn(Evaluate(funding.RecordCount - Form.MaxRows - Form.StartRow)) -1 p CFOUTPUT smallspan class=black10Viewing #Form.StartRow# to #funding.RecordCount# of b#funding.RecordCount#/b records found./font/small/span /cfoutput /cfif div align=left table width=761 border=0 tbody tr th background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif; align=leftspan class=black10Organization Name/th th background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif; align=leftspan class=black10Funding/th th background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif; align=leftspan class=black10Committe Approval/th /tr cfoutput query=funding startrow=#StartRow# maxrows=#Form.MaxRows# tr BGCOLOR=###IIF(funding.currentrow MOD 2, DE ('e7efef'), DE ('f7f7de'))# !--- td align=left valign=top background=http://intranet.neath-porttalbot.gov.uk/images/tablebg1.gif;sp an class=black10#Evaluate(CurrentRow)#/td --- td align=left valign=top span class=black10#orgname#/td td align=left valign=topspan class=black10#funding#/td td align=left valign=top span class=black10#commapproval#/td /tr /cfoutput/tbody/table - Original Message - From: Pascal Peters [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 11:34 AM Subject: RE: Correct Syntax for this piece of SQL ?? This should work on ORACLE8/CF4.5.1 (I'm developping for that platform right now) I can't make much of your error. Can you post your entire cfquery. CFQUERYPARAM is used for parameterized sql. It is usually good for performence and it helps to avoid sql hacks. cfsqltype describes the datatype of your column (in this case a VARCHAR2). You can check out the help or devnet (http://www.macromedia.com/desdev/articles/ben_forta_faster.html) -Oorspronkelijk bericht- Van: Ian Vaughan [mailto:[EMAIL PROTECTED] Verzonden: wo 26/02/2003 11:55 Aan: CF-Talk CC: Onderwerp: Re: Correct Syntax for this piece of SQL ?? Pascal Thanks for your solution but it does not work, I am using CF 4.5 and Oracle 8. I am getting the following error when using your code, is it not compatible with 4.5 ? and what is the need for cfqueryparam cfsqltype=CF_SQL_VARCHAR ?? Error Occurred While Processing Request Error Diagnostic Information CFQuery The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (61:1) to (62:59 - Original Message - From: Pascal Peters [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 10:12 AM Subject: RE: Correct Syntax for this piece of SQL ?? If one of your form variables is empty, it will always return all the records because you match '%%' (which means anything). If you want to match one of the criteria you entered, this should work: SELECT
RE: Correct Syntax for this piece of SQL ??
Just a stab in the dark: Is orgname always uppercase in your DB? If so, that might explain why searching by orgname always works but searching by the others doesn't. You might have to do this: ...AND UPPER(funding) LIKE UPPER('%#Form.funding#%') or EVEN (the same): ...AND UPPER(funding) LIKE '%#UCase(Form.funding)#%' etc. Thanks, André -Original Message- From: Ian Vaughan [mailto:[EMAIL PROTECTED] Sent: 25 February 2003 14:49 To: CF-Talk Subject: Correct Syntax for this piece of SQL ?? Hi I have the following form which is being used to search against my database table. When I search using the 'orgname' field and there are matches then results are returned. However if I search in the other two fields then it displays no results found when it should bring back results ? Is this because of my sql ?? CFQUERY datasource=liv8 name=funding cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100 select * from funding Where orgname LIKE UPPER('%#Form.orgname#%') AND funding LIKE UPPER('%#Form.funding#%') AND commapproval LIKE UPPER('%#Form.commapproval#%') ORDER BY recordid /CFQUERY This is my search form form method=POST action=/testpages/fundingresults.cfm name=search input type=Hidden name=MaxRows value=100 input type=hidden name=StartRow value=1 font color=#00 face=Verdanaspan class=black10 p input type=text class=mini name=orgname size=22 style=WIDTH: 200pxb Organization Name/b /p input type=text class=mini name=funding size=22 style=WIDTH: 200pxbSource of Funding/b p input type=text class=mini name=commapproval size=22 style=WIDTH: 200pxbCommittee Approval Date/b /p p input type=submit value=Search name=B1nbsp;nbsp; input type=reset value=Reset Form name=B2 p /form ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Correct Syntax for this piece of SQL ??
Try this: CFQUERY datasource=liv8 name=funding cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100 select * from funding cfif form.orgname gt Where orgname LIKE UPPER('%#Form.orgname#%') /cfif cfif form.funding gt cfif form.orgname gt ANDcfelseWHERE/cfif funding LIKE UPPER('%#Form.funding#%') /cfif cfif form.comapproval gt cfif form.orgname gt or form.funding gt ANDcfelseWHERE/cfif commapproval LIKE UPPER('%#Form.commapproval#%') /cfif ORDER BY recordid /CFQUERY This will generate a query based on which fields actually have values to search on. If I understand what you are wanting to do here. Larry Juncker Senior Cold fusion Developer Heartland Communications Group, Inc. [EMAIL PROTECTED] (515) 574-2122 CONFIDENTIALITY NOTICE The information contained in this e-mail is intended only for the use of the individual or entity to which it is addressed. This e-mail may contain information that is privileged, confidential and/or personal. If the reader of this message is not the intended recipient (or the employee or agent responsible to deliver it to the intended recipient), you are hereby notified that any dissemination, distribution, or copying of this communication is prohibited. If you have received this communication in error, please notify us at the e-mail listed above. -Original Message- From: Ian Vaughan [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 25, 2003 8:49 AM To: CF-Talk Subject: Correct Syntax for this piece of SQL ?? Hi I have the following form which is being used to search against my database table. When I search using the 'orgname' field and there are matches then results are returned. However if I search in the other two fields then it displays no results found when it should bring back results ? Is this because of my sql ?? CFQUERY datasource=liv8 name=funding cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100 select * from funding Where orgname LIKE UPPER('%#Form.orgname#%') AND funding LIKE UPPER('%#Form.funding#%') AND commapproval LIKE UPPER('%#Form.commapproval#%') ORDER BY recordid /CFQUERY This is my search form form method=POST action=/testpages/fundingresults.cfm name=search input type=Hidden name=MaxRows value=100 input type=hidden name=StartRow value=1 font color=#00 face=Verdanaspan class=black10 p input type=text class=mini name=orgname size=22 style=WIDTH: 200pxb Organization Name/b /p input type=text class=mini name=funding size=22 style=WIDTH: 200pxbSource of Funding/b p input type=text class=mini name=commapproval size=22 style=WIDTH: 200pxbCommittee Approval Date/b /p p input type=submit value=Search name=B1nbsp;nbsp; input type=reset value=Reset Form name=B2 p /form ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Correct Syntax for this piece of SQL ??
A bit complicated. Better WHERE 0=0 cfif Len(form.orgname) AND UPPER(orgname) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#Form.orgname#%) /cfif cfif Len(form.funding) AND UPPER(funding) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#Form.funding#%) /cfif cfif Len(form.commapproval) AND UPPER(commapproval) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=%#Form.commapproval#%) /cfif -Original Message- From: Larry Juncker [mailto:[EMAIL PROTECTED] Sent: dinsdag 25 februari 2003 15:59 To: CF-Talk Subject: RE: Correct Syntax for this piece of SQL ?? Try this: CFQUERY datasource=liv8 name=funding cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100 select * from funding cfif form.orgname gt Where orgname LIKE UPPER('%#Form.orgname#%') /cfif cfif form.funding gt cfif form.orgname gt ANDcfelseWHERE/cfif funding LIKE UPPER('%#Form.funding#%') /cfif cfif form.comapproval gt cfif form.orgname gt or form.funding gt ANDcfelseWHERE/cfif commapproval LIKE UPPER('%#Form.commapproval#%') /cfif ORDER BY recordid /CFQUERY This will generate a query based on which fields actually have values to search on. If I understand what you are wanting to do here. Larry Juncker Senior Cold fusion Developer Heartland Communications Group, Inc. [EMAIL PROTECTED] (515) 574-2122 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Correct Syntax for this piece of SQL ??
I think Lee said it too, you've got a basic logic flaw. Your query says all three criteria must be met. Did you mean to say: select * from funding Where ( orgname LIKE '%#Form.orgname#%' ) OR( funding LIKE '%#Form.funding#%' ) OR( commapproval LIKE '%#Form.commapproval#%' ) ORDER BY recordid You can use parenthesis to be MORE subtle, like if you want the second and third criteria to be required together: Where ( orgname LIKE '%#Form.orgname#%' ) OR ( ( funding LIKE '%#Form.funding#%' ) AND ( commapproval LIKE '%#Form.commapproval#%' ) ) On 2/25/03 6:48 AM, Ian Vaughan [EMAIL PROTECTED] wrote: Hi I have the following form which is being used to search against my database table. When I search using the 'orgname' field and there are matches then results are returned. However if I search in the other two fields then it displays no results found when it should bring back results ? Is this because of my sql ?? CFQUERY datasource=liv8 name=funding cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100 select * from funding Where orgname LIKE UPPER('%#Form.orgname#%') AND funding LIKE UPPER('%#Form.funding#%') AND commapproval LIKE UPPER('%#Form.commapproval#%') ORDER BY recordid /CFQUERY This is my search form form method=POST action=/testpages/fundingresults.cfm name=search input type=Hidden name=MaxRows value=100 input type=hidden name=StartRow value=1 font color=#00 face=Verdanaspan class=black10 p input type=text class=mini name=orgname size=22 style=WIDTH: 200pxb Organization Name/b /p input type=text class=mini name=funding size=22 style=WIDTH: 200pxbSource of Funding/b p input type=text class=mini name=commapproval size=22 style=WIDTH: 200pxbCommittee Approval Date/b /p p input type=submit value=Search name=B1nbsp;nbsp; input type=reset value=Reset Form name=B2 p /form ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4