Solved

Salary Amount Format

Posted on 2016-09-26
13
74 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

749 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