Re: SQL formatting quesion re: TO_CHAR()

From: Serge Egorov <egorov_at_rbac.kharkov.com>
Date: Fri, 18 Sep 1998 16:14:27 +0400
Message-ID: <36024EA3.7540FBE4@rbac.kharkov.com>

lewandow_at_uwm.edu wrote:

> I have a special problem here in the school system.
>
> The Big Bad State Education Office wants a report (1290) that looks like
> this: 9003091F000120003 ^ ^ ^^^ ^ ^ (record type '9')/(filler
> '003')/(grade '09')/(race code '1')/(gender 'F')/(number of records
> '00012')/(school number '0003')
>
> But I get this output:
>
> 9 003091F 00012 0003
>
> when I use the following SQL script:
> ------------
> SPOOL 1290OUT.TXT
> SET HEADING OFF
> SELECT
> '9'||
> to_char(T.SCH_CD, '000')||
> T.GRD_LVL||
> Student.ETHNC_CD||
> Student.GENDER_CD||
> to_char(COUNT(T.SDNT_ID), '00000')||
> to_char(T.SCH_CD, '0000')
> FROM
> Student, Thrd_Fri_Enrl T, WAREHS.Thrd_Fri_Dates
>
> It appears the TO_CHAR formatting statement automatically inserts a space

Yes

> before the value, even if you try using concatenation. Any way around this?

Yesuse

SELECT

      '9'||
       LPAD(T.SCH_CD, 3, '0')||
       T.GRD_LVL||
       Student.ETHNC_CD||
       Student.GENDER_CD||
       LPAD(COUNT(T.SDNT_ID), 5, '0')||
       LPAD(T.SCH_CD, 4, '0')

FROM --
Serge Egorov.
Awaiting at egorov_at_rbac.kharkov.com Received on Fri Sep 18 1998 - 07:14:27 CDT