Solved

Salary Amount Format

Posted on 2016-09-26
13
70 Views
Last Modified: 2016-10-03
I'm currently using this code:
SUBSTR(TO_CHAR(emp.pro_rate_total, '999990d99', 'NLS_NUMERIC_CHARACTERS = ''V '''), -9) Salary_Amount

The result are:
45864V00
13590V72
25935V00

How can I get the result to be 9 digits in length without any preceding 0's and without the V?
0
Comment
Question by:metalteck
  • 7
  • 4
  • 2
13 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41816359
>>How can I get the result to be 9 digits in length without any preceding 0's

LPAD with a <space> instead of the '0' from your previous question?  lpad(column,' ',9)

>>and without the V?
TO_CHAR(emp.pro_rate_total*100, '999990'
0
 

Author Comment

by:metalteck
ID: 41816402
Slightwv, when I use this code:
TO_CHAR(emp.pro_rate_total*100, '999990'

This is what I get
#######
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41816409
That means the value returned was larger than the format.  Expand the format mask to a maximum of 9 characters,

TO_CHAR(emp.pro_rate_total*100, '999999990'
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:metalteck
ID: 41816716
slightwv, that works, but I have 4 or 5 records where I'm still getting #########.
I checked and the value is 7 digits before multiplying it by 100.
There a way I can also get these values to be displayed?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41816723
Forgot the sign bit.

Try:
TO_CHAR(emp.pro_rate_total*100, 'fm999999990'
0
 

Author Comment

by:metalteck
ID: 41816873
slightwv, that now shows me the info with the large number.
Thank you, but that code is causing the rest of the numbers to be skewed.

I need to have all numbers to have a fixed length of 9, regardless if they are not 9 digits.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41816914
>>I need to have all numbers to have a fixed length of 9, regardless if they are not 9 digits.

If you added the lpad with spaces and things aren't lining up, then look at the font you are using.  Unless you are using a fixed width font, things won't line up perfectly.
0
 

Author Comment

by:metalteck
ID: 41816953
No, I did not use the lpad with spaces. How would I write that?

Unfortunately, all the salary amounts are required to have a fixed length.
The original code of:
SUBSTR(TO_CHAR(emp.pro_rate_total, '999990d99', 'NLS_NUMERIC_CHARACTERS = ''V '''), -9)

had everything lined up at 9 spaces, but just had the V which was causing errors.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 41817015
>>How would I write that?

You already know how to do that.

I posted it above and it is the same LPAD as your previous question...

From above:
LPAD with a <space> instead of the '0' from your previous question?  lpad(column,' ',9)

I had the syntax slightly wrong but you already know LPAD or you should from the previous questions.

Example where 123.45 is your starting number:
select lpad(to_char(123.45*100,'fm999999990'),9,' ') from dual;

Open in new window


>>The original code of:

The substr in that could be really bad.  If you had a number of 10 characters, you only grabbed the last 9:
SQL> select substr(1234567890,-9) from dual;

SUBSTR(12
---------
234567890

Open in new window

0
 
LVL 8

Expert Comment

by:Ghunaima
ID: 41818558
Lpad(trim(TO_CHAR(emp.pro_rate_total, '999999099')), 9, '') 

Try above. Your existing formula specifies that there is a decimal point before last two digits as V is normally used as virtual decimal place
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41818567
Ghunaima,

That will not work for a couple of reasons.
0
 
LVL 8

Expert Comment

by:Ghunaima
ID: 41818739
What are the values saved in column pro_rate_total?

If these contain decimal point or any non numeric character then my solution will not work.

Also replace '' with ' '

Lpad(trim(TO_CHAR(emp.pro_rate_total, '999999000')), 9, ' ')

If there is a decimal point following may be used

Lpad(replace(trim(TO_CHAR(emp.pro_rate_total, '9999990.00')), '.', ''), 9, ' ')
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41818831
>>If there is a decimal point following may be used

Isn't that pretty much the exact same thing I posted?  I'm not seeing where it is any more efficient.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

831 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