Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at the New York ColdFusion User Group (Feb. 2008) with:

ColdFusion SQL Error - [Table ....] Is Not Indexable By Name

Posted by Ben Nadel
Tags: ColdFusion, SQL

I was helping a co-worker of mine tracking down a ColdFusion error he was getting:

  • [Table (rows 1 columns ID, PREFIX, FIRST_NAME, MIDDLE_NAME, LAST_NAME, SUFFIX, WORKING_NAME): [ID: coldfusion.sql.QueryColumn@c4736a] [PREFIX:
  • coldfusion.sql.QueryColumn@17542e7] [FIRST_NAME:
  • coldfusion.sql.QueryColumn@17b5ee7] [MIDDLE_NAME:
  • coldfusion.sql.QueryColumn@93ab1f] [LAST_NAME:
  • coldfusion.sql.QueryColumn@c79c37] [SUFFIX:
  • coldfusion.sql.QueryColumn@e0eb96] [WORKING_NAME:
  • coldfusion.sql.QueryColumn@8af270] ] is not indexable by name

I hadn't seen this error before, so the phrase "indexable" immediately made me think about full text indexes and primary keys. After several minutes of moving CFTRY/CFCATCH tags around, we finally found a better error message. It said something like the SELECT column 'name' was not a reference in any of the FROM tables. It was suddenly obvious; in his Query of Queries, the 'name' column was not defined. I find it very funny when simple mistakes take a lot of debugging.

On another note though, I think it is very interesting that the error thrown by the Query of Queries CFQuery tag was different than the one available in CFCATCH. Well, maybe not different at the core, but worded very differently.



Reader Comments

I go this error this morning, however I got it when I tried inserting a structure to a row of a query as in:

<cfset Tmp = StructInsert(QueryName[CurrentRow],OtherQueryName,QueryValue,true)>

I was pretty much trying my luck, half-expected the error. I hope this helps.

I'm working on work-around - thanks!

Reply to this Comment

I recently was faced with the same problem, but for a different reason: I was trying to implement POIUtility into my application and in the ColumnList I left spaces after each comma. Removing the spaces fixed the problem.

Reply to this Comment

@kamil,

You're a brain-saver. Wow. I */knew/* my column existed as it was being CFDumped in the code immediately preceeding.

For people of the future... I had
listtoarray("col1, <SPACE> col2, <SPACE> Col3")

the fix:
LtA("col1,col2,col3")

Thus, the HTMLEditFormat(Query[Columns[ColID]][Query.CurrentRow]) was being fed the array w/o spaces

Reply to this Comment

Thought I would add to this...

I thought it was because I did not define the column in my query. I was wrong. I was outputting my query in CFScript using a for loop.

if(bldg_resultset.recordCount gt 0){
for(i=1;i<=bldg_resultset.recordCount;i++)
//ArrayAppend(bldgArray,bldg_resultset[i].bldgid); ArrayAppend(bldgArray,bldg_resultset.bldgid[i]);
}

The commented out code gave me this exact error, but instead of name it was '1', the value of the first i.

Reply to this Comment

@Brad,

Good point - the column name always has to come first... which is definitely counter-intuitive if you think of queries as "arrays" of "structs".

Reply to this Comment

I bounced into the same problem and it was even simpler but yet was a stumper. I had the columns listed correctly or so I thought. Only thing in my quest to have properly formatted code, I had spaces between each column. Don't ask! Thing was when it was it included the space in the error message but since i wasn't looking for it I didn't see them. Thanks for the tip!

Iain

Reply to this Comment

I had this same issue as well but from a different approach. I didn't have a misspelled name and the column was defined. In fact, I wasn't even really using that particular column in my query, although I was doing SELECT * to get the whole row.

I basically had a function that took an ID and a recordset as arguments. From a QoQ the recordset gets narrowed down, but it is highly likely that I will get back multiple rows still with the same ID.

Through a bunch of calculations and whatnot, I single out the one row I am interested in, save that row number to a variable, and attempt to return that row to the calling page, and this is where that same error came from.

You cannot return a row from a recordset like:
<cfreturn queryname[row_index]>

The trick around this is to create another column in your table and have an incremental counter in it. instead of saving 'CurrentRow' to the row_index variable (to store the current index) and returning queryname[row_index], you do another QoQ to find the row that contains the value row_index in your new column.

Then, you can return this entire query and you now have your singled out row...no more errors :)

Reply to this Comment

I encountered the same error, "..coldfusion is not indexable by LEFT(column name)..." in a regular query. Maybe this goes without saying to some but my original code:

SELECT art_key,left(art_title,50)
FROM cts_article

just needed a small tweak:

SELECT art_key,left(art_title,50) AS art_title
FROM cts_article

which I tried because this post mention how the query of queries had not defined the 'name' column.

[just posting in case others find their way here from a search on the same error]

Reply to this Comment

i have the same proble, but, i try to show the info of 100 columns, all have the similar name, p001, p002... to p100, i put first a column name total.
ok, for not make the same code 100 times, i sink that i can do something like 'print' the name and use like a variable o something and put to the name of the query ande take that valor.

<cfset data=query.columname> and i have that error.

<table>
<tr>
<td>PREGUNTA</td>
<td>ACIERTOS</td>
</tr>
<cfset info=structNew()>
<cfloop from="1" to="100" index="x">
<cfswitch expression="#Len(x)#">
<cfcase value="1"> <cfset preg='P00'&x> </cfcase>
<cfcase value="2"> <cfset preg='P0'&x> </cfcase>
<cfcase value="3"> <cfset preg='P'&x> </cfcase>
</cfswitch>
<cfset info.campo=resultados.#campo#>
<cfset campo='resultados.'&preg>
<cfdump var="#info#">
<tr>
<td>#preg#</td>
<td>
#resultados['#campo#'][0]#</td>
</tr>
</cfloop>
</table>

if i dont make that, i have to make the same code to make % and a little bar graph 100 times LOL.

i share my similar error and.. looking for if my idea is possible, thanks, ben naddel, u are awesome!. thanks por coding!.

you make the string 'The Power of Code' make sense. thanks.

Reply to this Comment

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.