in Query of Query, can't use LEFT() function?

[Bdog_]

Hi All,

I've got an LDAP query that I use for our employee directory. I then do a Query of Queries and do LEFT(SN,1) to separate out by first character of last name [A,B,C,D,E...] etc.

I had this working just fine in Railo CFM, but now I'm using Adobe CF and can't get that to work. I'm on CF9 Standard.

I've read the Query of Queries guide, but didn't find anything helpful there. Even when I do

'#LEFT(SN,1)#' as FirstCharacterOfLastName in my query, what I get is the value for the last row, not the value of each row as it's looping through the recordset.

Am I missing something? Any way to get that to work? Is that addressed in CF10?

thx.Ben

[Adam Cameron.]

You need to understand two things:

1) any CFML in your SQL string within a <cfquery> tag pair is processed by CF before the SQL string is passed to the DB driver.  So your #left()# expression won't be processed "per row", because it's processed before the DB engine does any "per row" operations.

2) QoQ's SQL support is tragically minimalist.  This lists all the functions it supports:

http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html...

With the addition of two string functions: UPPER() and LOWER().  Oh, and CAST() (if that can be seen as a function).

That's it.  That page in the docs describes all the functionality QoQ has.  There's nothing missing 9as far as I know).  That's it.  There's not much to it.

I've been lobbying Adobe for a number of years to pull finger and improve QoQ to the point that it's more than just a curio, but they don't seem interested.

As to why your code works on Railo?  Railo does some weird things, and doesn't stick to the precedent Adobe dictates their language should follow.  Depending on who you talk to, this is either a feature or a barrier to entry (I'm mostly ambivalent, but err towards the latter camp).  But perhaps Railo's QoQ processor supports LEFT().

Basically... you're gonna have to approach this differently: loop over the query with <cfloop> (etc) and update it row by row.  This is pretty much what QoQ is doing anyhow, so I don't think you'll see a performance degradation.  Well: you won't... because you can't do what you want to do with QoQ at all, I guess 😉

--

Adam

[Bdog_]

Hi Adam,

Thanks for weighing in, I appreciate it. I managed to do some inelegant stuff to get my code to work.

Could you give me an example of what you meant by "loop over the query with <cfloop> (etc) and update it row by row" though. I was writing into an array and then still had a messy time looping through in a grouped fashion.

Funny enough I see you can use GROUP in CFLOOP now in CF10, but we bought CF9 without an SA.

Thanks,

Ben

[Dan_Bracuk]

Regarding "Could you give me an example of what you meant by "loop over the query with <cfloop> (etc) and update it row by row" though."

<cfloop query = "someQuery">

<cfset QuerySetCell(someQuery, "aColumn", aValue, [currentrow])>

</cfloop>