MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Converting particular column into percentage with Symbol(%)

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 43
Topics: 9

PostPosted: Wed Aug 02, 2017 1:58 pm    Post subject: Converting particular column into percentage with Symbol(%) Reply with quote

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%   
 
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Wed Aug 02, 2017 2:57 pm    Post subject: Reply with quote

Suchay,

How is the column Rate defined? Decimal(8,7) ? what is the percentage if the rate column had a value of 9.1234567?
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 43
Topics: 9

PostPosted: Wed Aug 02, 2017 3:21 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Wed Aug 02, 2017 3:40 pm    Post subject: Reply with quote

Suchay,

Use the following SQL

Code:

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

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 43
Topics: 9

PostPosted: Wed Aug 02, 2017 4:13 pm    Post subject: Reply with quote

Kolusu,


Tried the above select got the below error

Code:
                                                     
QUERY MESSAGES:                                       
Argument '1' of scalar function 'TO_CHAR' is invalid.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Wed Aug 02, 2017 4:19 pm    Post subject: Reply with quote

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.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 43
Topics: 9

PostPosted: Wed Aug 02, 2017 4:42 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Wed Aug 02, 2017 5:06 pm    Post subject: Reply with quote

Suchay,

I guess your RATE column is NOT defined as decimal. Are sure your column is defined as decimal(7,7) ?
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 43
Topics: 9

PostPosted: Wed Aug 02, 2017 5:17 pm    Post subject: Reply with quote

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)
Back to top
View user's profile Send private message
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 43
Topics: 9

PostPosted: Wed Aug 02, 2017 5:18 pm    Post subject: Reply with quote

RATE DECIMAL(7, 7) NOT NULL WITH DEFAULT
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Wed Aug 02, 2017 5:22 pm    Post subject: Reply with quote

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%                                                   

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group