Solved

Salary Amount Format

Posted on 2016-09-26
13
76 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
Technology Partners: 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Merge join vs exist 3 37
Oracle Date 6 42
Deleting multiple child tables from parent in one SQL statement 20 56
DB Shutdown Automatically 11 36
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

739 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