Hi,

I have a table with the following two columns and sample data:

tran_date DATE;
digits NUMBER;

10/2/2007 0.000738889791980111
11/22/2007 0.00083740843091
12/11/2007 6.00083740843091

For a special output, I tried to concatenate the two columns using
the following SQL:

SELECT to_char(tran_date,'YYYYMMDD') || ',' || digits from
mytable;

The result is missing the "0" for two numbers starting with "0."
as in the following:

20071002,.000738889791980111
20071122,.00083740843091
20071211,6.00083740843091 (This is fine)

I know I may be able to do some decode or other manipulation, but
could someone tell me why the leading "0" is omitted from the query
output?

Thanks in advance.

Nick

<nickli2000 (AT) gmail (DOT) com> schreef in bericht
news:ab51ddb0-77cd-4b01-a899-4094a2995626 (AT) d21g2000prf (DOT) googlegroups.com...
I guess because of an implicit to_char of your digits column:

SQL>select to_char(0.1) from dual;

TO
--
,1

SQL>

(BTW: Note that in my case there is also a conversion of '.' to ',', due to
NLS settings)

Shakespeare

On Jan 2, 11:38*am, nickli2... (AT) gmail (DOT) com wrote:

Because the leading 0 to the left of the decimal point is not a
significant digit, thus it can be removed without affecting the actual
numeric value. If you really need that value stored in the database
it may be necessary to change the column definition to that of a
varchar2(30):

SQL> create table mytable(
2 tran_date date,
3 digits number
4 );

Table created.

SQL>
SQL> insert all
2 into mytable
3 values (to_date('10/02/2007','MM/DD/YYYY'), 0.000738889791980111)
4 into mytable
5 values (to_date('11/22/2007','MM/DD/YYYY'), 0.00083740843091)
6 into mytable
7 values (to_date('12/11/2007','MM/DD/YYYY'), 6.00083740843091)
8 select * From dual;

3 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> SELECT to_char(tran_date,'YYYYMMDD') || ',' || digits
2 from mytable;

TO_CHAR(TRAN_DATE,'YYYYMMDD')||','||DIGITS
-------------------------------------------------
20071002,.000738889791980111
20071122,.00083740843091
20071211,6.00083740843091

SQL>
SQL> drop table mytable;

Table dropped.

SQL>
SQL> create table mytable(
2 tran_date date,
3 digits varchar2(30)
4 );

Table created.

SQL>
SQL> insert all
2 into mytable
3 values (to_date('10/02/2007','MM/DD/YYYY'),
'0.000738889791980111')
4 into mytable
5 values (to_date('11/22/2007','MM/DD/YYYY'), '0.00083740843091')
6 into mytable
7 values (to_date('12/11/2007','MM/DD/YYYY'), '6.00083740843091')
8 select * From dual;

3 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> SELECT to_char(tran_date,'YYYYMMDD') || ',' || digits
2 from mytable;

TO_CHAR(TRAN_DATE,'YYYYMMDD')||','||DIG
---------------------------------------
20071002,0.000738889791980111
20071122,0.00083740843091
20071211,6.00083740843091

SQL>


David Fitzjarrell

On Jan 2, 1:49 pm, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:
Thanks for your help. Could you tell me why Oracle decides to omit the
leading "0" when concatenating the numbers and what settings affect
this?

nickli2... (AT) gmail (DOT) com wrote:
NO settings affect this and I've already informed you as to WHY. You
can scroll up and read the explanation again.


David Fitzjarrell

On Jan 2, 2:19*pm, nickli2... (AT) gmail (DOT) com wrote:


Changing the datatype to VARCHAR seems like overkill. Numeric data
should be stored in numeric data types. I would suggest using the
correct number format for the output. I think
set numformat 0.999999999999999999
should work. Or using that format pattern with the column command:
column digits format 0.999999999999999999
should give your desired result.

(note: I did not test this pattern, but the syntax of the commands is
correct.)

HTH,
ed

On Jan 2, 2:30*pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:
I have tested such a 'fix' and it doesn't do what the OP requests.
The concatenation operation, along with an implicit conversion, 'lops
off' the leading 0 (again, as it's not a significant digit).

Preserving the leading 0 is, at least in 10.2.0.3 and earlier
releases, only possible with the data stored as a varchar2.


David Fitzjarrell

On Jan 2, 3:38*pm, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:
I tested it and it does what he wants. There are two problems though.
First it inserts a space before the number, second is that you must
have a fixed number of digits after the decimal. The first can be
remedied using a substr and the second by using a format mask with the
maximum number of decimal places then using rtrim to remove the
trailing 0's

SQL> select to_char(sysdate,'YYYYMMDD')||','||to_char(0.8,'0.9 9999')
from dual;

TO_CHAR(SYSDATE,'
-----------------
20080102, 0.80000

SQL> select to_char(sysdate,'yyyymmdd')||','||
rtrim(substr(to_char(0.8,'0.99999'),2),'0') from dual;


TO_CHAR(SYSDATE,'YYYYMMDD')||','||RTR
-------------------------------------
20080102,0.8

Ken Denny schrieb:
You tested another workaround.

There are two problems though.
Look up in the SQL Reference about 'FM' format modifier.

SQL> select to_char(sysdate,'yyyymmdd')||','||to_char(0.8,'fm0 .99999')
from dual;

TO_CHAR(SYSDATE,'
-----------------
20080103,0.8



Best regards

Maxim

On Jan 2, 6:15*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:
You're right. I didn't read Ed's reply carefully.
Thanks Maxim. I haven't used the 'FM' before. Glad to know about it.