Re: How to create a Cobol flatfile in sqlplus

Manfred,

You'll have to use the concat operator (||) for this. Try something like:

  SELECT t,LPAD(ii*100,7,'0') || LPAD(i,3,'0')   FROM mstest;

This query should give you the output you have indicated in your test case. If you are working with strings, you will want to investigate the RTRIM function as well.

HTH,
Brian

Manfred Friedrich wrote:
>
> I would like to create a flatfile out of sqlplus
> from the following table:
>
> SQL> desc mstest
> Name Null? Type
> ------------------------------- -------- ----
> T VARCHAR2(5)
> II NUMBER(7,2)
> I NUMBER(3)
>
> SQL> select * from mstest;
>
> T II I
> ----- ---------- ----------
> M 7000,77 49
> K 17000,17 59
>
> The Output which I would like to see is
>
> M 0700077049
> K 1700017059
>
> I tried:
>
> SET NEWPAGE 0
> SET SPACE 0
> SET PAGESIZE 0
> SET ECHO OFF
> SET FEEDBACK OFF
> SET HEADING OFF
> column i format 000
> column ii format 00000v00
>
> and the result is
>
> M 0700077 049
> K 1700017 059
>
> There is one extra space before each mumeric value.
>
> How can I avoid this?
>
> TIA.
>
> Manfred
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================