Note - this is CF 5. I'm not sure if you would call this an undocumented
feature but....
I have a Q of a Q that pulls data from a cached and rather lengthy stored
procedure. Because the columns in the Q of a q are determined by the user,
I chose to build a string and pass it into the q of a q using
"preservesingelquotes". At time for version 2 the client mentioned that the
search fields for "last name" appear to be case-sensitive. Sure enough,
it's a well-known fact that q of a q is case-sensitive, and the "UPPER" and
"LOWER" keywords are provided to fix me right up. Dutifully, I changed my
query of a query from:
SELECT * FROM GetData
WHERE TID <> '' AND last_name IN ('#strVAr#')
To....
SELECT * FROM GetData
WHERE TID <> '' AND UPPER(last_name) IN ('#UCASE(strVar)#') AND 1 = 1
This caused CF 5 to throw an error:
query manipulation error = 0
UPPER is not a number
Neither UPPER or LOWER would work. I checked and when I ran the query
directly - it also would not work. Suddenly I remember that UPPER and LOWER
may have been introduced in CFMX - so perhaps THAT is why they didn't work.
However, the more I thought about it, the more that error puzzled me. So on
a wild guess I decided to try the following:
SELECT * FROM GetData
WHERE TID <> '' AND 1(last_name) IN ('#LCASE(strVar)#') AND 1 = 1
Would you believe that this converted my last_name columne correctly and
resulted in a record being retrieved? I went on up the scale and stumbled
upon:
SELECT * FROM GetData
WHERE TID <> '' AND 3(last_name) IN ('#UCASE(strVar)#') AND 1 = 1
So, as near as I can tell, 1( ) in a q of a q resuts in a Lcase and 3
results in a Ucase. My best guess is that Q of a Q implements a library and
the words LOWER and UPPER are constants to functions in the library - which
contains numerous items that were not implemented in CF 5. Anyone have any
thoughts on this? Can anyone duplicate my result?
-Mark
Mark A. Kruger, MCSE, CFG
www.cfwebtools.com
www.necfug.com
mxc.blogspot.com
..no more brochures!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=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
