Copy link to clipboard
Copied
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
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")#
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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>
Copy link to clipboard
Copied
<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
Copy link to clipboard
Copied
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")#
Copy link to clipboard
Copied
<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.
Copy link to clipboard
Copied
<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
Copy link to clipboard
Copied
If the concatonation is static text to a db field, a simple way to do it is in the output, not in the query.
Copy link to clipboard
Copied
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