Skip to Main Content

FM and zeros

NF_D99F_D90F_D00F_FM_D99F_FM_D90F_FM_D00
.9# 0.90## 0.90## 0.90##0.9##0.90##0.90#
.95# 0.95## 0.95## 0.95##0.95##0.95##0.95#
1# 1.00## 1.00## 1.00##1.##1.00##1.00#
1.05# 1.05## 1.05## 1.05##1.05##1.05##1.05#
1.1# 1.10## 1.10## 1.10##1.1##1.10##1.10#

I learned something new today about an Oracle function I have been using for almost 20 years... Never a dull moment :-)

If you use to_char like this: to_char(1, '990D99') you get "1.00", with some leading spaces.
However if you use this: to_char(1, 'fm990D99') you get "1.".

The Oracle documentation on Format Models states "FM: Fill Mode. Oracle uses trailing blank characters and leading zeroes to fill format elements to a constant width. [...] The FM modifier suppresses the above padding in the return value of the TO_CHAR function.".
I found no mention of removing trailing zeros, so still not sure why FM creates this difference.

Trailing decimal character

NF_FM_D99
1#1#

In the "FM and zeros" example above, the "fm990D99" formatting of 1 results in "1.".
To remove the decimal character from that result, you could use a dirty "nls_numeric_characters" trick - see the code below. Not recommended, but fun :-)

ORA-43918 - This argument must be a literal

SX1X2X
1,234.561234.56-1234.56
1234.56-1234.561234.56
99999999
some_string---

If you remove the materialize hint (see query below), the coalesce will result in a "ORA-43918: This argument must be a literal" error (tested on database versions 12.2 and 18XE).
This is caused by the first "to_number ... default null on conversion error" statement, but I'm not really sure why. I found a reference that suggests the cursor_sharing_exact hint may fix this, but that doesn't seem to apply to this case.
Also not sure why the materialize hint works here, to me it seems to have nothing to do with it. But thanks Arnoud, you saved the day by suggesting that fix :-)

Code

Region

Identification
Sequence
Title
Type
Source
Source Type
Region Source

Region

Identification
Sequence
Title
Type
Source
Source Type
Region Source

Region

Identification
Sequence
Title
Type
Source
Source Type
Region Source