Improve company productivity with a Business Account.Sign Up

x
?
Solved

Unexpected data returned when using the TO_CHAR function

Posted on 2015-01-07
3
Medium Priority
?
285 Views
Last Modified: 2015-01-08
Gurus,

I'm getting unexpected data returned when using the TO_CHAR function.

Statement:
select distinct
  TO_CHAR(SPECIAL_CHAR('$'||PRSPRSC/100,'fm99999.00'))
 from PRS,ART
      where prsartkeyi = artkeyi and artanrc = 23901;

Result: $.5
Expected Result $0.50

How do I fix this?
0
Comment
Question by:xbox360dp
  • 2
3 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40536696
what does your SPECIAL_CHAR function do?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 40536703
I don't have your tables, data or function,
but to illustrate how TO_CHAR can be used for format a value into monetary form

SELECT DISTINCT TO_CHAR((prsprsc / 100), 'fm$99999.00')
  FROM (select 50 prsprsc from dual)
0
 
LVL 79

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 40536753
One tweak to the above example to add the 0 to the left of the decimal:
'fm$99990.00'

I'm guessing that it is  SPECIAL_CHAR that is messing with your format mask.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the dā€¦
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

606 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