Why can't I use a dynamic column name in this query?

Question

I am trying to build a cfquery using a dynamic column name:

<cfquery dbtype="query" name="getColLength">
    SELECT MAX(LEN( #ListGetAt(ColumnList, index)# ))
    FROM query
</cfquery>

However, this gives me the following error:

Encountered "MAX ( LEN (. Incorrect Select List,

Why doesn't this work? I tried surrounding the #ListGetAt(InputColumnList, 1)# with a <cfoutput> tag, but that didn't help. Any ideas?

Update:

This doesn't seem to be a problem with the dynamic column name. If I try hardcoding the column, I get the same error:

SELECT MAX(LEN(MyColumnName))
FROM query

What's wrong with this syntax?

Further Update:

This works:

SELECT Max(MyColumnName)
FROM query

While this doesn't:

SELECT LEN(MyColumnName)
FROM query

The SELECT LEN gives me this error:

Encountered "(. Incorrect Select Statement, Expecting a 'FROM', but encountered '(' instead, A select statement should have a 'FROM' construct. 

I suppose I can use the SELECT MAX query and then use the coldfusion len function... but why doesn't this work?

Answer 1

The CF9 docs make reference to CHARACTER_LENGTH. However it is unclear as to whether that is a supported function or just a reserved word. I have CF8 setup and your query fails with both LEN and CHARACTER_LENGTH but if you have CF9 setup then give CHARACTER_LENGTH a try.

Not sure how large your recordset is but if this doesn't work you can try a simple loop over the query and keep the higher length as you go.

<cfset maxLength = 0 />
<cfloop query="recordset">
    <cfif len(column) gt maxLength>
        <cfset maxLength = len(column) />
    </cfif>
</cfloop>

However since comparisons are the big performance killer another approach for large recordsets is to use struct keys to only do writes, then compare unique lengths at the end resulting in many fewer comparisons.

For example:

<cfset lenStruct = {} />
<cfloop query="recordset">
    <cfset lenStruct[len(column)] = "" />
</cfloop>
<cfset maxLength = 0 />
<cfloop list="#structKeyList(lenStruct)#" index="length">
    <cfif length gt maxLength>
        <cfset maxLength = length />
    </cfif>
</cfloop>

Answer 2

How about:

SELECT MAX(myLength) 
FROM (
    SELECT LENGTH(MyColumnName) As myLength
    FROM table
)