View previous topic :: View next topic |
Author |
Message |
Suchay Beginner
Joined: 29 Jun 2017 Posts: 43 Topics: 9
|
Posted: Wed Aug 02, 2017 1:58 pm Post subject: Converting particular column into percentage with Symbol(%) |
|
|
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 |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12357 Topics: 75 Location: San Jose
|
Posted: Wed Aug 02, 2017 2:57 pm Post subject: |
|
|
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 |
|
|
Suchay Beginner
Joined: 29 Jun 2017 Posts: 43 Topics: 9
|
Posted: Wed Aug 02, 2017 3:21 pm Post subject: |
|
|
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 |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12357 Topics: 75 Location: San Jose
|
Posted: Wed Aug 02, 2017 3:40 pm Post subject: |
|
|
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 |
|
|
Suchay Beginner
Joined: 29 Jun 2017 Posts: 43 Topics: 9
|
Posted: Wed Aug 02, 2017 4:13 pm Post subject: |
|
|
Kolusu,
Tried the above select got the below error
Code: |
QUERY MESSAGES:
Argument '1' of scalar function 'TO_CHAR' is invalid. |
|
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12357 Topics: 75 Location: San Jose
|
Posted: Wed Aug 02, 2017 4:19 pm Post subject: |
|
|
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 |
|
|
Suchay Beginner
Joined: 29 Jun 2017 Posts: 43 Topics: 9
|
Posted: Wed Aug 02, 2017 4:42 pm Post subject: |
|
|
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 |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12357 Topics: 75 Location: San Jose
|
Posted: Wed Aug 02, 2017 5:06 pm Post subject: |
|
|
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 |
|
|
Suchay Beginner
Joined: 29 Jun 2017 Posts: 43 Topics: 9
|
Posted: Wed Aug 02, 2017 5:17 pm Post subject: |
|
|
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 |
|
|
Suchay Beginner
Joined: 29 Jun 2017 Posts: 43 Topics: 9
|
Posted: Wed Aug 02, 2017 5:18 pm Post subject: |
|
|
RATE DECIMAL(7, 7) NOT NULL WITH DEFAULT |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12357 Topics: 75 Location: San Jose
|
Posted: Wed Aug 02, 2017 5:22 pm Post subject: |
|
|
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 |
|
|
|
|