concatenating string in cache query

[jason_y]

Hi,

I was wondering if I concatenate strings within the query I write in a <cfquery> tag with dbtype="query"?

I want to do something similar to the following:

<cfquery name="a_query" dbtype="query">

     SELECT

          ('$ ' + TotalProfit) AS TotalProfit2

     FROM SomeCachedQuery

</cfquery>

I can do this if the query is run on the SQL server, but not with the cached query.If this is not possible, is there some code I can wirte that does the same thing?

Thanks!

Jason

[Dan_Bracuk]

Since you can do it in the original sql, you should.  If you want to do it in ColdFusion, look at the query functions such as queryaddcolumn and querysetcell.

[BKBK]

I was wondering if I concatenate strings within the query I write in a <cfquery> tag with dbtype="query"?

I want to do something similar to the following:

<cfquery name="a_query" dbtype="query">

     SELECT

          ('$ ' + TotalProfit) AS TotalProfit2

     FROM SomeCachedQuery

</cfquery>

I can do this if the query is run on the SQL server, but not with the cached query.If this is not possible

That is quite possible. In fact, it is so common you will come across it all over the place.

Just to confirm: what you're trying to do should be something like

<cfquery name="SomeQuery" datasource="someDSN">
     SELECT TotalProfit
     FROM someTable
</cfquery>

<cfquery name="a_query" dbtype="query">
     SELECT ('$ ' + TotalProfit) AS TotalProfit2
     FROM SomeQuery
</cfquery>

[Adam Cameron.]

<cfquery name="a_query" dbtype="query">
     SELECT ('$ ' + TotalProfit) AS TotalProfit2
     FROM SomeQuery
</cfquery>

Does this actually work for you?  It doesn't for me, unless I case TotalProfit to be a varchar, first.

--

Adam

[-__cfSearching__- · Accepted solution]

Does this actually work for you?  It doesn't for me,

unless I case TotalProfit to be a varchar, first.

As Adam said, you need to cast the value to a varchar first. Either in the original query or within the QoQ:

SELECT '$ '+ CAST(TotalProfit AS VARCHAR) AS TotalProfit2

FROM SomeQuery

However, you could just use NumberFormat() in your output. Unless there is some reason you need to do this in a QoQ..

#NumberFormat(SomeQuery.TotalProfit, "$-9999999.00")#

[BKBK]

<cfquery name="a_query" dbtype="query">
     SELECT ('$ ' + TotalProfit) AS TotalProfit2
     FROM SomeQuery
</cfquery>

Does this actually work for you?  It doesn't for me, unless I case TotalProfit to be a varchar, first.

--

Adam

Yes, Adam, it works for me. But then, I take it for granted TotalProfit is of type char or varchar. The title and Jason_y's opening phrase("I was wondering if I concatenate strings") give that impression.

[Adam Cameron.]

<cfquery name="a_query" dbtype="query">
     SELECT ('$ ' + TotalProfit) AS TotalProfit2
     FROM SomeQuery
</cfquery>

Does this actually work for you?  It doesn't for me, unless I case TotalProfit to be a varchar, first.

Yes, Adam, it works for me. But then, I take it for granted TotalProfit is of type char or varchar. The title and Jason_y's opening phrase("I was wondering if I concatenate strings") give that impression.

Well... sure.  To concatenate one string ("$") to something else, both are going to have to end up being strings at some stage.  But it seems unlikely that a column that:

a) is having a dollar-sign applied to the beginning of its value;

b) is called "TotalProfit";

is going to be a string to start with.

Still... stranger things have happened, I guess.

--

Adam

[Dan_Bracuk]

If the concatonation is static text to a db field, a simple way to do it is in the output, not in the query.

[jason_y]

Hi All,

Thanks for your help. The TotalProfit column in my original query is a numeric field and casting it in the QofQ did the trick. The reason I wanted to add the dollar sign in the query instead of a <cfouput> is I wanted to use the result of the query and bind it to a cfgrid (HTML version). This tag had attributes that allow you to add numberformat / dollarformat, but they're only for the Flash version of the cfgrid. It was unavailable for the HTML version. I considered using the Flash version, but this version did not allow you to bind data to the grid. I wanted the page navigation / column sorting that binding allows.

I'm not too sure why cfgrid have different attributes for different grid types. I kinda wish the binding and the number format both exist at the same time.

Jason