Re: SqlPlus and formatted output

[Quoted] Most likely your output is 4 lines. Any computed alphanumeric expression will automatically end up as a column with 80 characters. So you need to have the substr expressions aliased and a column xyz format a<whatever> before your sql statement,

or better still use column statements before the statement so you don't need the lpad disaster.
[Quoted] You can do it with either a format mask or to_char directly. This is merely an issue of using sufficient 0's in your format mask. In your [Quoted] case (provided the last digit should always be printed) you would need 0000000000009 or (I'm never sure about this) 0999999999999.

I have attempted to cleanup your sql-statement a little according to my conventions. Don't see this as criticism, but in it's current form for me it is hardly legible and difficult to understand (as no example is included) what the output actually should look like.

 SELECT 'A123456'
||acct_cd
||'EDP '
||SUBSTR

                (LPAD(to_char(SUM (prev_period_net_comm
                                                  )
                                        ),13, '0'
                            ),1,13
                )


               ||'         '


               ||'                         EMBEDED TEXT                1234

>  '
> ||'A123456'||acct_cd||'                            EDP                '
> ||SUBSTR(LPAD(COUNT(*),13, '0'),1,13)||'         '||
> '                         EMBEDED TEXT                4120 12345OTHER
> '
>   FROM table
>  WHERE ind = 'Y'
>    AND acct_cd NOT LIKE 'Z0%'
>  GROUP BY acct_cd
>  ORDER BY acct_cd;

Regards,

Sybrand Bakker, Oracle DBA

<stemp1ar_at_my-deja.com> wrote in message news:88d42u$et1$1_at_nnrp1.deja.com...

> I would like to run a pl/sql or sqlplus script and create a file that
> must be formatted and fixed positioning...the script below is close but
> every 4 line has a blank line?
>
> Is there an easier way considering that I must have constants in the
> select stmt?
>
> PLEASE RESPOND TO:  stemp1ar_at_yahoo.com
>
[Quoted] > (example script)
> set LINESIZE 80
> set pagesize 0
> set trimspool on
> set feedback off
> set termout off
> set wordwrap on
> set trim on
>
> spool test.dat
>
> SELECT 'A123456'||acct_cd||'
> EDP                '
> ||SUBSTR(LPAD(to_char(SUM(prev_period_net_comm)),13, '0'),1,13)
> ||'         '||
> '                         EMBEDED TEXT                1234 12345OTHER
>  '
> ||'A123456'||acct_cd||'                            EDP                '
> ||SUBSTR(LPAD(COUNT(*),13, '0'),1,13)||'         '||
> '                         EMBEDED TEXT                4120 12345OTHER
> '
>   FROM table
>  WHERE ind = 'Y'
>    AND acct_cd NOT LIKE 'Z0%'
>  GROUP BY acct_cd
>  ORDER BY acct_cd;
>
>  spool off;
>
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.