Re: Concatenate numbers

From: Ed Prochak <edprochak_at_gmail.com>
Date: Wed, 2 Jan 2008 12:30:04 -0800 (PST)
Message-ID: <1dc576dd-0ca4-4a85-aaea-a3d81c21ef59@h11g2000prf.googlegroups.com>


On Jan 2, 2:19 pm, nickli2..._at_gmail.com wrote:
> On Jan 2, 1:49 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > On Jan 2, 11:38 am, nickli2..._at_gmail.com wrote:
>
> > > 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

[]
>
> 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?

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 Received on Wed Jan 02 2008 - 14:30:04 CST

Original text of this message