8 Replies Latest reply on Nov 30, 2009 11:26 AM by jason_y

    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

        • 1. Re: concatenating string in cache query
          Dan Bracuk Level 5

          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.

          1 person found this helpful
          • 2. Re: concatenating string in cache query
            BKBK Adobe Community Professional & MVP

            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>

            • 3. Re: concatenating string in cache query
              Adam Cameron. Level 5

              <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

              • 4. Re: concatenating string in cache query
                -==cfSearching==- Level 4

                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")#

                • 5. Re: concatenating string in cache query
                  BKBK Adobe Community Professional & MVP

                  <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.

                  • 6. Re: concatenating string in cache query
                    Adam Cameron. Level 5

                    <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

                    • 7. Re: concatenating string in cache query
                      Dan Bracuk Level 5

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

                      • 8. Re: concatenating string in cache query
                        jason_y Level 1

                        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