Can I concatenate strings in a ColdFusion query?

Question

I am familiar with a similar syntax in SQL Server to concatenate strings in my result set:

SELECT 'foo' + bar AS SomeCol FROM SomeTable 

I would like to do something like this in a ColdFusion query request:

 <cfquery name="qOptimize" dbtype="query"> select image_id AS imageId, '#variables.img_root#' + image_id + '.' + image_ext AS fullImage, '#variables.img_root#' + image_id + 't.' + image_ext AS thumbnailImage, from qLookup order by imageId asc </cfquery> 

This is part of the service consumed by the Flex application, so I optimize the result of the stored procedure used elsewhere in the application before returning to the client - remove unused columns and compile image URLs from some dynamic path information.

I could write a new stored procedure that takes the root of the image as a parameter and does all this , and I will probably work for performance reasons , but the question is still shaking me. I have not yet found a syntax that works, so I am wondering if this is possible.

When I try the above, I get the following error:

Query Of Queries syntax error.
Invalid selection list, Invalid selection column,

Has anyone done this? Is it possible, perhaps with a different syntax?

Answer 1

Yes it is possible. I think the problem is that image_id is most likely a numeric value. If you pronounce it as varchar, then it will be fine.

 <cfquery name="qOptimize" dbtype="query"> select image_id AS imageId, '#variables.img_root#' + cast(image_id as varchar) + '.' + image_ext AS fullImage, '#variables.img_root#' + cast(image_id as varchar) + 't.' + image_ext AS thumbnailImage from qLookup order by imageId asc </cfquery> 

Answer 2

I think the error you are talking about is related to the comma at the last concatenation at the end of thumbnailImage.

My only $ 0.002

Answer 3

or even if you need concatenation not at the level of cold infusion, but in the request itself:

in my example, I already have a query result object that I want to insert into the query query. resul tobject query has 10 columns of data and 1000 rows, column names are c1, c2, c3, c4, c5, ...

  <cfset dbzeilerest = "2 4 - 3"><!--- beg qoq to concatenate those columns ---> <cfset sqlcodehere = "("> <cfloop list="#dbzeilerest#" delimiters="," index="t"> <cfif val(t) GT 0> <cfset sqlcodehere = sqlcodehere & "C" & val(t) & " || "> <cfelse> <cfset sqlcodehere = sqlcodehere & "'" & t & "' || "> </cfif> <!--- concat in coldfusion sql qoq: ( C2 || ' ' || C4 || ' ' || '-' || ' ' || C3 ) as combii ---> </cfloop> <cfset sqlcodehere = sqlcodehere & " '') as combii"> <cfquery name="dbtexttemp2" dbtype="query"> SELECT DISTINCT #PreserveSingleQuotes(sqlcodehere )# FROM dbtexttemplistequery </cfquery>