CODEWITHSUNDEEP
sqlcoldfusion
Adam Tuttle
Adam Tuttle

Reputation: 19804

Is it possible to do string concatenation in a ColdFusion Query of Queries?

I'm familiar with this sort of syntax in SQL Server, to concatenate strings in my result set:

SELECT 'foo' + bar AS SomeCol
FROM SomeTable

I would like to do something similar inside a ColdFusion Query of Queries:

<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 a service consumed by a Flex application, so I'm optimizing the result of a stored procedure used elsewhere in the application before returning to the client -- stripping out unused columns, and compiling image URLs from some dynamic path information.

I could write a new stored procedure that takes the image root as a parameter and does all of this, and I probably will for performance reasons, but the question is still nagging me. I haven't found a syntax that works yet, so I wonder if it's possible.

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

Query Of Queries syntax error.
Encountered "from. Incorrect Select List, Incorrect select column,

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

Upvotes: 6

Views: 6784

Answers (3)

Raffael Meier
Raffael Meier

Reputation: 309

or even when you need the concatenate not on coldfusion level but in the query itself:

in my example, I have already a query result object which I want to dig into with query of queries. the query resul tobject has 10 columns of data and 1000 rows, the 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>

Upvotes: 1

Jason
Jason

Reputation: 17956

Yes this is possible. I think the problem is that image_id is most likely a numeric value. If you cast it as an varchar then it should 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>

Upvotes: 11

Edmundo
Edmundo

Reputation:

I think the error you mention is due to the comma at the last concatenation, at the end of thumbnailImage.

Just my $0.002

Upvotes: 5

Related Questions