ms-sql ( if statements )
I dont see why my if ..else statements are causing errors when performing a query against an ms-sql database CODE: if #form.what# = 1 select * from candidate where else select * from advertiser where if #form.by# = 1 if #form.what# eq 1 first_name like '%#form.dastring#%' or last_name like '%#form.dastring#%' else company_name like '%#form.dastring#%' else if #form.by# = 2 if #form.what# eq 1 email like '%#form.dastring#%' else contact_email like '%#form.dastring#%' else id = '#form.dastring#' ERROR: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'else'. The error occurred in C:\Inetpub\wwwroot\executivesontheweb\admin\usermanagement\search.cfm: line 78 76 :contact_email like '%#form.dastring#%' 77 :else 78 :id = '#form.dastring#' 79 : 80 :/cfquery SQLif 1 = 1 select * from candidate where else select * from advertiser where if 2 = 1 if 1 eq 1 first_name like '%mystring%' or last_name like '%mystring%' else company_name like '%mystring%' else if 2 = 2 if 1 eq 1 email like '%mystring%' else contact_email like '%mystring%' else id = 'mystring' ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201357 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: ms-sql ( if statements )
If you want to do this kind of dynamic query, I believe you need to be using the CASE WHEN THEN ELSE END format for ms-sql, but that will be a beast to create. If you want to do this with CF creating a dynamic query, you'd do something like this: cfquery cfif form.what = 1 select * from candidate where cfelse select * from advertiser where /cfif cfif form.by = 1 cfif form.what eq 1 first_name like '%#form.dastring#%' or last_name like '%#form.dastring#%' cfelse company_name like '%#form.dastring#%' /cfif cfelse cfif form.by = 2 cfif form.what eq 1 email like '%#form.dastring#%' cfelse contact_email like '%#form.dastring#%' /cfif cfelse id = '#form.dastring#' /cfif On Apr 4, 2005 10:15 AM, Protoculture [EMAIL PROTECTED] wrote: I dont see why my if ..else statements are causing errors when performing a query against an ms-sql database CODE: if #form.what# = 1 select * from candidate where else select * from advertiser where if #form.by# = 1 if #form.what# eq 1 first_name like '%#form.dastring#%' or last_name like '%#form.dastring#%' else company_name like '%#form.dastring#%' else if #form.by# = 2 if #form.what# eq 1 email like '%#form.dastring#%' else contact_email like '%#form.dastring#%' else id = '#form.dastring#' ERROR: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'else'. The error occurred in C:\Inetpub\wwwroot\executivesontheweb\admin\usermanagement\search.cfm: line 78 76 :contact_email like '%#form.dastring#%' 77 :else 78 :id = '#form.dastring#' 79 : 80 :/cfquery SQLif 1 = 1 select * from candidate where else select * from advertiser where if 2 = 1 if 1 eq 1 first_name like '%mystring%' or last_name like '%mystring%' else company_name like '%mystring%' else if 2 = 2 if 1 eq 1 email like '%mystring%' else contact_email like '%mystring%' else id = 'mystring' ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201365 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: ms-sql ( if statements )
If you look at the SQL statement as it was parsed you can see that it isn't valid sql immediately. select * from candidate where else select * from advertiser where if 2 = 1 if 1 eq 1 first_name like '%mystring%' or last_name like '%mystring%' else company_name like '%mystring%' else if 2 = 2 if 1 eq 1 email like '%mystring%' else contact_email like '%mystring%' else id = 'mystring' Also the if else construct in MSSQL Server can't be used inline. In this case you can use the CASE WHEN construct but judging by the dynamic nature of the full query you should replace your if then statements with CFIF CFELSE statements and send a complete query: cfif form.what eq 1 select * from candidate where cfelse select * from advertiser where /cfif cfif form.by eq 1 etc ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201366 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
