[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

Dynamic Sort

I am trying to create a sortable query, but get this error message.
ODBC Error Code = 37000 (Syntax error or access violation)

<cfset sortby ="CASE WHEN (photo1 <> '0' AND Photo1Auth <> 1) OR (photo2 <> '0' AND Photo2Auth <> 1) OR (photo3 <> '0' AND Photo3Auth <> 1) OR (photo4 <> '0' AND Photo4Auth <> 1) OR (photo5 <> '0' AND Photo5Auth <> 1) THEN 1 ELSE 0 END DESC">

SELECT *
FROM table
ORDER BY #sortby#

If I hardcode it, it works fine

SELECT *
FROM table
ORDER BY CASE WHEN (photo1 <> '0' AND Photo1Auth <> 1) OR (photo2 <> '0' AND Photo2Auth <> 1) OR (photo3 <> '0' AND Photo3Auth <> 1) OR (photo4 <> '0' AND Photo4Auth <> 1) OR (photo5 <> '0' AND Photo5Auth <> 1) THEN 1 ELSE 0 END DESC

Any ideas??
0
JRockFL
Asked:
JRockFL
  • 2
1 Solution
 
_agx_Commented:
CF may be automatically escaping the single quotes.  ie replacing '  with ''.   What happens if you use ?

ORDER BY #PreserveSingleQuotes(sortby)#


Though on a personal note, I try and avoid this type of dynamic clause for a few reasons.  Using <cfqueryparam> can boost performance and helps avoid sql injection risks.
0
 
_agx_Commented:
> Using <cfqueryparam>  
..    _instead_
0
 
JRockFLAuthor Commented:
_agx_

Great thinking with the cfqueryparam, I forgot all about that. It didn't work though, same results.
#PreserveSingleQuotes(sortby)# worked though.

Thank you!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now