Solved

Add 0 to end of Number

Posted on 2016-09-22
21
72 Views
Last Modified: 2016-10-03
I have this code working:
rpad(trunc(sum(prt.hours)),4)
and the result is:
2004
1996
2016

But I need for the results to be:
200400
199600
201600

How can I adapt the code to add the additional 0s?
0
Comment
Question by:metalteck
  • 8
  • 6
  • 2
  • +4
21 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 41810786
Try rpad(trunc(sum(prt.hours)),6)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41810788
rpad(your_column,6,'0')
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41810800
Try:

rpad(trunc(sum(prt.hours)),4)*100
0
 

Author Comment

by:metalteck
ID: 41810984
Guys, one thing I forgot to mention too.
If the value is 0, I also need it to display as 000000.
How can I modify the suggestions you mentioned to include that?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41810999
rpad returns a string (varchar2).  It will display as 000000 unless something is turning it back into a number.

What tool/product are you using to display the results?
0
 

Author Comment

by:metalteck
ID: 41811005
I'm using sql developer.
@slightwv, when I use your solution, the 0's do not display.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41811006
Apply a custom format of ";;000000".

Zero will display as expected.

If you omit the ;; part, values which were originally only 3 digits (999 or less) will gain leading zeros eg original 999 with added trailing zeros becomes 99900 but formatted as 000000 will become 099900.

Thanks
Rob H
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41811016
>>@slightwv, when I use your solution, the 0's do not display.

It works for me.  Make sure you aren't doing any implicit data type conversions back to a number.  Make the RPAD the outer most function call.

If you still can't get it working, post the SQL you are using.

Untitled.jpg
0
 

Author Comment

by:metalteck
ID: 41811031
Here is sql code I'm using:
SELECT prt.employee,
rpad(trunc(sum(prt.hours)),6,'0')  
       FROM prod.prtime prt
       WHERE( prt.tr_date >= add_months(trunc(sysdate, 'mm'),-12)
             AND   prt.tr_date < trunc(sysdate)  )
       and prt.pay_sum_grp in ('REG', 'REGH','OT','ED','OREN')
group by prt.employee
order by prt.employee

and here are the results I'm getting.
240000

240000
240000

196900
172200
0
 

Author Comment

by:metalteck
ID: 41811039
Also, if the result is 8, slightwv, when I use your solution, its coming back as 800000
It should be coming in as 000800
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41811040
>>and here are the results I'm getting.

What results are you wanting? Given the original question you wanted to append zeros to the end value to a maximum length of 6.

If that isn't what you want, please explain the requirements.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41811042
>>It should be coming in as 000800

OH, you want to pre-pend.  That isn't what your original data showed.

Change RPAD to LPAD.

SELECT prt.employee,
 lpad(trunc(sum(prt.hours)),6,'0')  ...
0
 
LVL 17

Expert Comment

by:xtermie
ID: 41811152
times 10 will give you the extra zero
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41811158
one more correction:

SELECT prt.employee,
  lpad(trunc(sum(prt.hours)*100),6,'0')  ...
0
 
LVL 4

Expert Comment

by:Abhimanyu Suri
ID: 41811164
If you really want to handle as per the length of summation, please use case or decode in your SQL

for example :

SQL> select decode(length(&sum_prt_hrs),1,rpad(lpad(&sum_prt_hrs,3,0),6,0),2,rpad(lpad(&sum_prt_hrs,3,0),6,0),rpad(&sum_prt_hrs,6,0)) from dual;
Enter value for sum_prt_hrs: 24
Enter value for sum_prt_hrs: 24
Enter value for sum_prt_hrs: 24
Enter value for sum_prt_hrs: 24
old   1: select decode(length(&sum_prt_hrs),1,rpad(lpad(&sum_prt_hrs,3,0),6,0),2,rpad(lpad(&sum_prt_hrs,3,0),6,0),rpad(&sum_prt_hrs,6,0)) from dual
new   1: select decode(length(24),1,rpad(lpad(24,3,0),6,0),2,rpad(lpad(24,3,0),6,0),rpad(24,6,0)) from dual

DECODE
------
024000

SQL> /
Enter value for sum_prt_hrs: 8
Enter value for sum_prt_hrs: 8
Enter value for sum_prt_hrs: 8
Enter value for sum_prt_hrs: 8
old   1: select decode(length(&sum_prt_hrs),1,rpad(lpad(&sum_prt_hrs,3,0),6,0),2,rpad(lpad(&sum_prt_hrs,3,0),6,0),rpad(&sum_prt_hrs,6,0)) from dual
new   1: select decode(length(8),1,rpad(lpad(8,3,0),6,0),2,rpad(lpad(8,3,0),6,0),rpad(8,6,0)) from dual

DECODE
------
008000

SQL> /
Enter value for sum_prt_hrs: 201
Enter value for sum_prt_hrs: 201
Enter value for sum_prt_hrs: 201
Enter value for sum_prt_hrs: 201
old   1: select decode(length(&sum_prt_hrs),1,rpad(lpad(&sum_prt_hrs,3,0),6,0),2,rpad(lpad(&sum_prt_hrs,3,0),6,0),rpad(&sum_prt_hrs,6,0)) from dual
new   1: select decode(length(201),1,rpad(lpad(201,3,0),6,0),2,rpad(lpad(201,3,0),6,0),rpad(201,6,0)) from dual

DECODE
------
201000

SQL> /
Enter value for sum_prt_hrs: 2016
Enter value for sum_prt_hrs: 2016
Enter value for sum_prt_hrs: 2016
Enter value for sum_prt_hrs: 2016
old   1: select decode(length(&sum_prt_hrs),1,rpad(lpad(&sum_prt_hrs,3,0),6,0),2,rpad(lpad(&sum_prt_hrs,3,0),6,0),rpad(&sum_prt_hrs,6,0)) from dual
new   1: select decode(length(2016),1,rpad(lpad(2016,3,0),6,0),2,rpad(lpad(2016,3,0),6,0),rpad(2016,6,0)) from dual

DECODE
------
201600
0
 

Author Comment

by:metalteck
ID: 41811583
@slightwv, I need to make a correction.
if the value is 8, it does not need to come across as 00800, it just needs to come across as 800.
The code of : rpad(trunc(sum(prt.hours)),6,'0')
Works, but if I use that, a value of 8 will be come as 800000.

What else can be tweak so that I can get 8 to be 800 and still have all values padded to the right 6 spaces regardless of the end result.
0
 
LVL 4

Expert Comment

by:Abhimanyu Suri
ID: 41811640
Remove lpad clause in decode statement for length 1 and change rpad to rpad(n,3,0),, that way all single digit results will be appended with 00
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41811707
>>if the value is 8, it does not need to come across as 00800, it just needs to come across as 800.
>>if the result is 8, slightwv, when I use your solution, its coming back as 800000 It should be coming in as 000800

Sorry but you have two different requirements for the exact same input.

I cannot take 8 and produce both 000800 and 800 at the same time.

To further confuse your requirements, above you posted:
If the value is 0, I also need it to display as 000000.

If 8 should produce 800, then 0 should produce 000.

You have been given the basics to allow you to produce your own solution.  It will likely be a combination of multiplication and lpad/rpad.

You need to be clear in your EXACT requirements.

Once you clarify your requirements, we can post solutions.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 41811747
>>The code of : rpad(trunc(sum(prt.hours)),6,'0')

Also, that isn't the latest code I posted.

Here is what I understand the requirements to be:
-Take the sum, multiply it by 100.
-Left pad the results with '0' to make a fixed length value of 6 characters.

Given 8, the last code I posted in #a41811158 it becomes:  
select lpad(8*100,6,'0') from dual;
0
 

Author Comment

by:metalteck
ID: 41812362
I apologize for the confusion slightwv.
I published the solutions that you provided to me  to my vendor and they adjusted the results.
You are correct, if the value is 8, it should be viewed as 800.
If the value is 0, then it too will be viewed as 000.
I'll let you know how everything goes.
0
 
LVL 8

Assisted Solution

by:Ghunaima
Ghunaima earned 250 total points
ID: 41819137
Try following code. Always add nvl to avoid problems when null is returned by the expression

TO_CHAR(NVL(trunc(sum(prt.hours)), 0)*100, '000000')
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now