Metalteck
asked on
Add 0 to end of Number
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?
Try rpad(trunc(sum(prt.hours)) ,6)
rpad(your_column,6,'0')
Try:
rpad(trunc(sum(prt.hours)) ,4)*100
rpad(trunc(sum(prt.hours))
ASKER
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?
If the value is 0, I also need it to display as 000000.
How can I modify the suggestions you mentioned to include that?
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?
What tool/product are you using to display the results?
ASKER
I'm using sql developer.
@slightwv, when I use your solution, the 0's do not display.
@slightwv, when I use your solution, the 0's do not display.
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
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
ASKER
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
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
ASKER
Also, if the result is 8, slightwv, when I use your solution, its coming back as 800000
It should be coming in as 000800
It should be coming in as 000800
>>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.
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.
>>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') ...
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))
times 10 will give you the extra zero
one more correction:
SELECT prt.employee,
lpad(trunc(sum(prt.hours)* 100),6,'0' ) ...
SELECT prt.employee,
lpad(trunc(sum(prt.hours)*
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(l pad(&sum_p rt_hrs,3,0 ),6,0),2,r pad(lpad(& sum_prt_hr s,3,0),6,0 ),rpad(&su m_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(l pad(&sum_p rt_hrs,3,0 ),6,0),2,r pad(lpad(& sum_prt_hr s,3,0),6,0 ),rpad(&su m_prt_hrs, 6,0)) from dual
new 1: select decode(length(24),1,rpad(l pad(24,3,0 ),6,0),2,r pad(lpad(2 4,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(l pad(&sum_p rt_hrs,3,0 ),6,0),2,r pad(lpad(& sum_prt_hr s,3,0),6,0 ),rpad(&su m_prt_hrs, 6,0)) from dual
new 1: select decode(length(8),1,rpad(lp ad(8,3,0), 6,0),2,rpa d(lpad(8,3 ,0),6,0),r pad(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(l pad(&sum_p rt_hrs,3,0 ),6,0),2,r pad(lpad(& sum_prt_hr s,3,0),6,0 ),rpad(&su m_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(l pad(&sum_p rt_hrs,3,0 ),6,0),2,r pad(lpad(& sum_prt_hr s,3,0),6,0 ),rpad(&su m_prt_hrs, 6,0)) from dual
new 1: select decode(length(2016),1,rpad (lpad(2016 ,3,0),6,0) ,2,rpad(lp ad(2016,3, 0),6,0),rp ad(2016,6, 0)) from dual
DECODE
------
201600
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
ASKER
@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.
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.
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
>>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 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.
>>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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.