Re: SQL formatting quesion re: TO_CHAR()

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 16 Sep 1998 19:59:15 GMT
Message-ID: <36111878.102096677@192.86.155.100>

A copy of this was sent to lewandow_at_uwm.edu (if that email address didn't require changing) On Wed, 16 Sep 1998 19:28:55 GMT, you 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
>WHERE (T.SCH_CD in ('033', '032')
>AND T.SDNT_ID=Student.SDNT_ID
>AND Thrd_Fri_Dates.THRD_FRI_DT=T.THRD_FRI_DT)
>GROUP BY T.SCH_CD, T.GRD_LVL,
> Student.ETHNC_CD, Student.GENDER_CD;
>SPOOL OFF
>SET HEADING ON
>/
>-------------------
>
>It appears the TO_CHAR formatting statement automatically inserts a space
>before the value, even if you try using concatenation. Any way around this?
>

the space is for the sign character that could be there. ltrim(to_char(t.sch_cd,'000')) will get rid of it...

>Thanks in advance,
>
>Kent
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Sep 16 1998 - 14:59:15 CDT