1. 

   werner, Member

   Hi

   I have a field with the data type double. Somehow I cannot display the correct number of decimals (it only shows two in the report). I need to show five decimals and so resorted to the FORMAT(number, 5)option. Unfortunately, this results in left aligning of my data in the column.

   Any suggestions as to how to get it to right align the field?

   Here is what I did in the select statement:

   SELECT tx_spncp_substance.name as 'Substance', tx_spncp_substance.type as 'Type', FORMAT(tx_spncp_quota.amount,5) as 'Quota in kg'

   Thanks so much!

   Werner
2. 

   myDBR Team, Key Master

   Werner
   FORMAT-function converts the numeric column to string (which causes the left alignment). It's better to separate the formatting of the output from the actual query. Separation keeps your SQL code cleaner and is therefore easier to maintain.

   Use the 'dbr.colstyle' to display all 5 decimals.

   select 'dbr.colstyle', 3, '%.5f'
   
   SELECT tx_spncp_substance.name as 'Substance',
   
           tx_spncp_substance.type as 'Type',
   
           tx_spncp_quota.amount as 'Quota in kg'
   
   from xxx

   See Applying Styles from documentation

   --
   myDBR Team
3. 

   werner, Member

   Thanks very much. What is needed if I have multiple columns for formatting? Ie, col 3, 4 etc or do I need to add a dbr.colstyle for each column

   BTW: found a missing ; on line 1 of your code ;)
4. 

   myDBR Team, Key Master

   Thanks very much. What is needed if I have multiple columns for formatting? Ie, col 3, 4 etc or do I need to add a dbr.colstyle for each column

   You put that for columns which do not use the default formatting (2 decimals for floating numbers without extra styles). In most cases the default formatting is fine though.

   BTW: found a missing ; on line 1 of your code ;)

   Well, the code was for SQL Server which does not need semicolons ;)

   --
   myDBR Team
5. 

   werner, Member

   Thanks very much again. Really appreciate your speedy help. Greetings from HOT and SUNNY Namibia!

Reply

You must log in to post.