Re: SQL*Plus report formatting

"Matt B." wrote:
>
> "Jon Schlatter" <*jon-schlatter_at_stamats.com> wrote in message
> news:tpf2879kjbqm8c_at_corp.supernews.com...
> > I tried the TO_NUMBER as described, but cannot get it to work properly.
> > Here is a brief view of my select statement, maybe you can correct my error.
> >
> > SELECT P.CUSTOMER_REF,P.JOB_MANAGER_REF, P.PROJECT_NO, TO_NUMBER(P.JOB_NO,
> > '99')
>
> IIRC, TO_NUMBER is one argument only. Just feed it P_JOB_NO. TO_NUMBER is for
> converting strings to numbers so that it can be evaluated as a number in any
> boolean conditions, in order-bys, etc. For formatting the output to two digits
> you need to use the "column" and "format" commands. It's been ages and my
> memory's fuzzy on this one, but it's something like (before your SQL statement)
> this:
>
> column p_job_no format 99
>
> -Matt

Sorry - cannot see rest of the thread, so this might be irrelevant. Matt's solution is fine. Here's another:

If the datatype of JOB_NO is numeric, use

        LTRIM(TO_CHAR(P.JOB_NO, '99'))
If the datatype of JOB_NO is text (e.g. VARCHAR2), then wrap it in TO_NUMBER:
        LTRIM(TO_CHAR(TO_NUMBER(P.JOB_NO), '99')) This assumes there are no alphabetics in any job numbers, or you'll get an Oracle error.

Cheers
JR Received on Wed Sep 19 2001 - 09:15:17 CDT