Re: Concatenate numbers

<nickli2000_at_gmail.com> schreef in bericht news:ab51ddb0-77cd-4b01-a899-4094a2995626_at_d21g2000prf.googlegroups.com...
> 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
>
>
>

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