Solved

Salary Amount Format

Posted on 2016-09-26
13
56 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
 

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now