LINESIZE - File Output 3

[pnad]

This is my final query:

SET FEEDBACK off
SET HEADING off
SET TERM off
SET VERIFY off
SET ECHO OFF
SET SPACE 0

set linesize 140

col col3 format a140 truncate
col col2 format a140 truncate
col col1 format a140 truncate

SPOOL C:\FTPtoBank\text.txt

select '01' || '987' || '1234512345' ||
rpad(substr(a.check_number, 2, 10) || ' ', 10) ||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(a.check_amt * 100, 0), 11, '0') || ' ', 11),
rpad(' ',15) || 'V' ||
rpad(' ',80) as col3
from table a, table b
where a.tran_id = b.tran_id
UNION
select '01' || '987' || '1234512345' ||
lpad(count(a.check_number),10,0)||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(sum(a.check_amt) * 100, 0), 11, '0') || ' ', 11) ||
rpad(' ',96) as col2,
null as col3
from table a, table b
where a.tran_id = b.tran_id
group by a.check_date
UNION
select '01' || '987' || '9999999999' ||
lpad(count(a.check_number),10,0)||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(sum(a.check_amt) * 100, 0), 11, '0') || ' ', 11) ||
rpad(' ',96) as col1,
null as col3
from table a, table b
where a.tran_id = b.tran_id
group by a.check_date;

SPOOL off

SET PAGESIZE 14
SET TERM on
SET VERIFY on
SET FEEDBACK on
SET HEADING on

exit



The file that is outputted as a result of the query does not have a linesize of 140. In the first SELECT, it has more than 15 spaces before the 'V' and more than 80 spaces after. How do I set the linesize so each line is only 140 characters.

Please advise.

Thanks.

 

[maxmouses]

Thank you so much.....I really needed that.