0

This is my query i have used to get the value as money. Nut when concat value getting above exception. The query is

select to_char(b.balance,'9999.'||d.number_of_decimal_places) from balance b, decimal d

Am stuck with this problem.

  • What is `number_of_decimal_places` - the number of digits (1, 2, 3...) or a model like 9, 99, 999 ? – Alex Poole Mar 18 '16 at 12:11
  • decimal places such as 9, 99, 999 –  Mar 18 '16 at 12:14
  • Can you add the actual table contents to the question? If decimal - which is an invalid table name - has multiple rows, presumably you have a join condition you haven't shown, so a more complete example would be helpful. – Alex Poole Mar 18 '16 at 12:21
  • Can you check that there are no invalid format masks in the `decimal` table? – MT0 Mar 18 '16 at 12:24
  • 1
    is d.number_of_decimal_places a char field having padded spaces that will mess up the mask? Try running select '9999.'||d.number_of_decimal_places from decimal d and see what mask it is actually generating... – Michael Broughton Mar 18 '16 at 12:29

3 Answers3

1

If you have numeric number_of_decimal_places values like 1, 2, 3 etc. then you are constructing a format model like, for example, '9999.2' instead of '9999.99'.

You can convert that integer value to the format model with rpad or lpad:

select to_char(b.balance,'9999.'||rpad('9', d.number_of_decimal_places, '9'))
from balance b, decimal d

Or for trailing zeros:

select to_char(b.balance,'9999.'||rpad('0', d.number_of_decimal_places, '0'))
from balance b, decimal d

If you have string number_of_decimal_places values like '9', '99', '999' etc. then the concatenation you have will work unless you have an invalid value in one of the rows, which would be any character other than a 9 or a 0.

That includes spaces, which you could have in a varchar2 or char field. Either way you could remove those with trim:

select to_char(b.balance,'9999.'||trim(d.number_of_decimal_places))
from balance b, decimal d

But if you have any other characters then you will need to identify and correct the data in those rows; and even with spaces it would be better to fix the data if it's a varchar2 column.

It would be better to use new-style joins; I haven't changed these examples because it isn't clear if you are doing a cartesian product or have just omitted the join conditions.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Thanks alex. It work's. But am facing one problem. When am having balance as 0 then the result returns as .00 . Not 0.00. If i using masking as to_number(.....) for 10 and 100 is working. If balance is 1000 it raising an error as "Invalid number". Hope you understand my problem... –  Mar 18 '16 at 15:23
  • @MuthuVikki - to see '0.00' you need the fixed part to to be `'9990.'`Not sure what you mean about 1000, that's fine with your four-digit fixed mask; more that 10000 will show as hashes though. Neither will give invalid number? You don't have `to_number()` in your question though so don't know quite what you are doing.. – Alex Poole Mar 18 '16 at 15:29
0

If number_of_decimals returns a value like 2 then:

SELECT TO_CHAR( b.balance, RPAD( '9999.', 5 + d.number_of_decimals, '9' ) )
FROM   balance b
       CROSS JOIN
       decimal d
MT0
  • 143,790
  • 11
  • 59
  • 117
0

For whatever reason, the concatenation of '9999.'||d.number_of_decimal_places is generating an invalid mask. We can only guess at the actual table values, presence of spaces, or whatever else may be causing issues with what it is doing.

So your solution is to run:

select '9999.'||d.number_of_decimal_places from decimal d

See what the actual format mask is that you are generating, and adjust as necessary.

Michael Broughton
  • 4,045
  • 14
  • 12