Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Salary Amount Format

Posted on 2016-09-26
13
Medium Priority
?
86 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
13 Comments
 
LVL 77

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 77

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

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 77

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 77

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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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 77

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 77

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

715 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