1. 

   shem, Member

   I wanted the values in the rows to be prefixed with a dollar sign, and at the same time to use dbr.sum to show totals at the bottom of the column.
   I am getting the following error:
   The command dbr.sum uses column "Amount Promised" which is not numeric (it's char). Non-numeric data prevented the calculation
   The command dbr.sum uses column "Amount Collected" which is not numeric (it's char). Non-numeric data prevented the calculation.

   Here is my code
   

       select 'dbr.sum',
   
                                  'Amount Promised',
   
                                  'Amount Collected'
   
       ;
   
         SELECT `Collector Name`                                                AS 'Collector Name',
   
                CONCAT('$', SUM(`Amount Promised`))                             AS 'Amount Promised',
   
                CONCAT('$', SUM(`Amount Collected`))                            AS 'Amount Collected'
   
           FROM tmp_table_ptp
   
       GROUP BY `Collector Name`
   
       ORDER BY `Amount Promised` DESC;
   
   

   p.s. I am using myDBR 6.9.4 (build 5133), and while one machine causes this error, another machine with the exact same build does not.
2. 

   myDBR Team, Key Master

   Hi,
   when you use CONCAT('$', SUM(`Amount Promised`)), it will turn a numeric column to character column. This will change behavior of the column (calculations, sorting etc).

   Instead of changing the datatype, use the dbr.colstyle to change the appearance of the column:

   If the column is an integer:
   

   select 'dbr.colstyle', 'Amount Promised', 'Amount Collected', '$ %d';

   or if you have decimals:
   

   select 'dbr.colstyle', 'Amount Promised', 'Amount Collected', '$ %.2f';

   --
   myDBR Team
3. 

   shem, Member

   thanks

Reply

You must log in to post.