Re: Formatting Ouput - Select Statement

In article <tcrp3ct86gb6ea_at_corp.supernews.com>, Jennmar Corp <here_at_work.com> writes:
>I have to pull information out of the database and format it to fit the
>standard layout for an upload file.
>
>Example:
>Format number len=5 Left Justified A####
> filler len=2 Spaces
> dept len=3 Left Justified A##
> filler len=2 Spaces
> etc...
>Output
>12345..123..Next Element
>
>Can I do this within the context of a select statement or is there an easier
>way to retrieve and then format the output?
>
>select number, dept, etc. from table1 ?
>
>Thanks!
>
>

By default character fields are left justified and numbers are right justified so if you want to create a fixed format output file you could do something like this:

select to_char(number_col,'00009')||' '||rpad(dept,3,' ')||' ' etc....

You might want to check the SQL*Plus manual under the set command to see the formatting commands for pagesize, linesize, heading, feedback, etc... You use these commands to eliminate pagebreaks and headings, put all columns on one record, eliminate headings when pagesize not 0, and eliminate the feedback line from your output spool file or screen.

The column command is where you normally set the format and sometime heading for columns, column dept format a3 heading Department.

             column cost format 9,999.99

• Mark D. Powell --
  • Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net