Be seen. Boost your question’s priority for more expert views and faster solutions

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?

rpad(trunc(sum(prt.hours))

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?

If the value is 0, I also need it to display as 000000.

How can I modify the suggestions you mentioned to include that?

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

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

SELECT prt.employee,

rpad(trunc(sum(prt.hours))

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

It should be coming in as 000800

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.

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))

for example :

SQL> select decode(length(&sum_prt_hrs

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

new 1: select decode(length(24),1,rpad(l

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

new 1: select decode(length(8),1,rpad(lp

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

new 1: select decode(length(201),1,rpad(

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

new 1: select decode(length(2016),1,rpad

DECODE

------

201600

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))

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.

>>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.

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.

TO_CHAR(NVL(trunc(sum(prt.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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;