Converting particular column into percentage with Symbol(%)
Select messages from
# through # FAQ
[/[Print]\]

MVSFORUMS.com -> Database

#1: Converting particular column into percentage with Symbol(%) Author: Suchay PostPosted: Wed Aug 02, 2017 1:58 pm
    —
Need to convert column Rate into the percentage with symbol '%' as shown below, we have used CONCAT(CHAR(DEC(RATE*100,3,2 )),'%') is there any better way to achieve the result



Code:

SELECT RATE,CONCAT(CHAR(DEC(RATE*100,3,2 )),'%') AS PERCENT
FROM   Table1     
             
      RATE  PERCENT   
----------  -------   
 0.0400000   4.00%   
 0.0425000   4.25%   
 0.0450000   4.50%   
 0.0475000   4.75%   
 0.0500000   5.00%   
 0.0525000   5.25%   
 0.0550000   5.50%   
 0.0575000   5.75%   
 0.0600000   6.00%   
 0.0625000   6.25%   
 0.0675000   6.75%   
 0.0725000   7.25%   
 0.0775000   7.75%   
 0.0825000   8.25%   
 

#2:  Author: kolusuLocation: San Jose PostPosted: Wed Aug 02, 2017 2:57 pm
    —
Suchay,

How is the column Rate defined? Decimal(8,7) ? what is the percentage if the rate column had a value of 9.1234567?

#3:  Author: Suchay PostPosted: Wed Aug 02, 2017 3:21 pm
    —
Kolusu,

Rate column is defined as DECIMAL(7,7)

kolusu wrote:
what is the percentage if the rate column had a value of 9.1234567?

zero chance

#4:  Author: kolusuLocation: San Jose PostPosted: Wed Aug 02, 2017 3:40 pm
    —
Suchay,

Use the following SQL

Code:

SELECT RATE                                       
      ,TO_CHAR(RATE * 100,'99.99') || '%' AS PERCENT
  FROM TABLE                         
  ;                 

#5:  Author: Suchay PostPosted: Wed Aug 02, 2017 4:13 pm
    —
Kolusu,


Tried the above select got the below error

Code:
                                                     
QUERY MESSAGES:                                       
Argument '1' of scalar function 'TO_CHAR' is invalid.

#6:  Author: kolusuLocation: San Jose PostPosted: Wed Aug 02, 2017 4:19 pm
    —
Suchay,

I am not sure as to why you enclose your own text in quote tags. I have been editing them. Use QUOTE tags when you wanted to quote other person's replies.

When you say something does not work, You need to show us the complete error messages along with the input. Run the query in spufi and show me the complete messages.

#7:  Author: Suchay PostPosted: Wed Aug 02, 2017 4:42 pm
    —
Kolusu,

Please find below info

Code:

SELECT RATE                   
      ,TO_CHAR(RATE,'99.99')   
FROM   Table       

---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -171, ERROR:  THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 1 
         OF TO_CHAR IS INVALID                                                 
DSNT418I SQLSTATE   = 42815 SQLSTATE RETURN CODE                               
DSNT415I SQLERRP    = DSNXOBFA SQL PROCEDURE DETECTING ERROR                   
DSNT416I SQLERRD    = 30 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION             
DSNT416I SQLERRD    = X'0000001E'  X'00000000'  X'00000000'  X'FFFFFFFF'       
         X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION

#8:  Author: kolusuLocation: San Jose PostPosted: Wed Aug 02, 2017 5:06 pm
    —
Suchay,

I guess your RATE column is NOT defined as decimal. Are sure your column is defined as decimal(7,7) ?

#9:  Author: Suchay PostPosted: Wed Aug 02, 2017 5:17 pm
    —
Code:

Use EXEC to continue; SQL to view/edit SQL; SAVE ; SIZE                       
 Table Name: Env.Table                                                     
 Max Rows to Select  ===> 2000        (* = ALL ROWS)                           
                                                                               
 SEL  COLUMN NAME        TYPE(LEN)        ORDER (A/D) WHERE            More: > 
 ---  ------------------ ------------------ --    -   ----+----1----+----2----+
 ___  RATE               DECIMAL(7,7)

#10:  Author: Suchay PostPosted: Wed Aug 02, 2017 5:18 pm
    —
RATE DECIMAL(7, 7) NOT NULL WITH DEFAULT

#11:  Author: kolusuLocation: San Jose PostPosted: Wed Aug 02, 2017 5:22 pm
    —
Suchay,

Well make sure you query for TO_CHAR is also referring the same ENV.TABLE

alternatively you can run this in spufi and see if you get the correct results
Code:

SELECT TO_CHAR(0.0400000 * 100,'99.99') || '%' AS PERCENT 
  FROM SYSIBM.SYSDUMMY1                                   
  ;                                                       
---------+---------+---------+---------+---------+---------
PERCENT                                                   
---------+---------+---------+---------+---------+---------
  4.00%                                                   



MVSFORUMS.com -> Database


output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1

Powered by phpBB © 2001, 2005 phpBB Group