CFSQL and calculated columns

[mrhatch]

The code:

<cfsearch collection="meetings" name="meetinglist">
<cfquery dbtype="query" name="test">
SELECT url, "#GetFileFromPath(key)# as filename
FROM meetinglist
</cfquery>

The error:

Query manipulation error code = 0
Invalid SQL
SQL= select url, "102203.pdf" as filename from meetinglist

Datasource = ""

The question:
Is it possible to insert a column into a query using CFSQL? I have tried a number of times to do this without any success. What I am trying to do is take the search results and insert a column which has only the file names in it.

TIA
Michael Hatch

[davidjmedlock]

Not sure what you mean by CFSQL, but I think what you need to do is use single quotes around the file name instead of double quotes. SQL doesn't recognize double quotes as string delimiters.

[mrhatch]

Originally Posted by davidjmedlock

Not sure what you mean by CFSQL, but I think what you need to do is use single quotes around the file name instead of double quotes. SQL doesn't recognize double quotes as string delimiters.

CFSQL is the query language supported in the Query of Queries.

I tried changing the double quotes to single quotes and now get a new error:

unknown exception condition

PCodeRuntimeContextImp::executeSQLTagCFQuery::endTag

[davidjmedlock]

Ah, I've used query of queries before, but I've never referred to it or heard it referred to as CFSQL... Learn something new every day, I guess.

At any rate, come to think of it, CFSQL does have some limitations. I know aggregate functions don't exactly work that well. So, this solution may work, but it may not be pretty. (This is assuming you want the same value for filename for every row in the query):

<cfsearch collection="meetings" name="meetinglist">
<cfquery dbtype="query" name="test">
SELECT url
FROM meetinglist
</cfquery>

<cfset FileNames = ArrayNew(1)>
<cfloop query="test">
  <cfset FileNames[CurrentRow] = GetFileFromPath(key)>
</cfloop>
<cfset a = QueryAddColumn(test, "filename", FileNames)>

That should add a column with the needed value to every row in the query... It's not elegant and not tested, so no guarantees there....

[mrhatch]

Thanks Dave,

This actually works. I had to tweak a little for syntax but it works. I am still mildly annoyed that the original syntax doesn't work. I will have to try it at home on MX. Stuck with 5.0 here at work.

Originally Posted by davidjmedlock

Ah, I've used query of queries before, but I've never referred to it or heard it referred to as CFSQL... Learn something new every day, I guess.

At any rate, come to think of it, CFSQL does have some limitations. I know aggregate functions don't exactly work that well. So, this solution may work, but it may not be pretty. (This is assuming you want the same value for filename for every row in the query):

<cfsearch collection="meetings" name="meetinglist">
<cfquery dbtype="query" name="test">
SELECT url
FROM meetinglist
</cfquery>

<cfset FileNames = ArrayNew(1)>
<cfloop query="test">
  <cfset FileNames[CurrentRow] = GetFileFromPath(key)>
</cfloop>
<cfset a = QueryAddColumn(test, "filename", FileNames)>

That should add a column with the needed value to every row in the query... It's not elegant and not tested, so no guarantees there....

[davidjmedlock]

Originally Posted by mrhatch

Thanks Dave,

This actually works. I had to tweak a little for syntax but it works. I am still mildly annoyed that the original syntax doesn't work. I will have to try it at home on MX. Stuck with 5.0 here at work.

Yeah, I figured it wasn't 100% syntactically correct. I didn't have much of a way to test it. I'd be interested to know if MX handles query of queries any better, though. I want to move to that at work, but we've got a large app that needs extensive testing before we do and not enough time to test it...

Glad I could be of (some) help...