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?

If you add a column alias does it help e.g. `MAX(LEN(MyColumnName)) as Foo` — Conrad Frix, Feb 01 '11 at 15:42
Did you try the word LENGTH() instead of LEN(). LEN() is CFML. LENGTH() is sql standard. — webRat, Feb 01 '11 at 16:02
Give us sample data please? What does the column list look like? — webRat, Feb 01 '11 at 16:08
@froadie - QoQ do not support an aggregate `length` function AFAIK. http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html#WSc3ff6d0ea77859461172e0811cbec0e4fd-7fcc — Leigh, Feb 01 '11 at 16:15
@webRat - I am not near a CF box to verify it. But I believe ACF is one step behind there ;) — Leigh, Feb 01 '11 at 16:22
@Leigh: Yeah, ACF9 throws same error above, but this code works fine in Railo ( http://cfml.pastebin.com/zcKdPV4X ) — webRat, Feb 01 '11 at 16:50

score 0 · Answer 1 · answered Feb 01 '11 at 15:45

0

How about:

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

answered Feb 01 '11 at 15:45

Joel Etherton

37,325
10
89
104

Doesn't work :( -` Query Of Queries syntax error. Encountered "(. ` – froadie Feb 01 '11 at 16:01
@froadie - QoQ do not support subqueries either. Unless the number of records is small, you may be better off doing this in a db query. – Leigh Feb 01 '11 at 18:27

Dan Roberts · Accepted Answer · 2011-02-01T16:36:33.353

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>

ACF9: Encountered "CHARACTER_LENGTH. Incorrect Select List, Incorrect select column, — webRat, Feb 01 '11 at 16:51

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

2 Answers2