Formatting columns in a mixed query

From: Neil <npluckne_at_ford.com>
Date: 23 Apr 2001 14:54:25 GMT
Message-ID: <Xns908CA1EC0856824361267ford@19.5.30.190>

I have a small problem - the query below runs fine from the SLECT part onwards. What I need to do is be able to format the column headings as per the first part of the query but the way I've done it does not seem to work. Can any one tell me where I'm going wrong please??

Thanks!

Neil Plucknett

Begin Query:
SET heading on
SET feedback off
SET linesize 700
COLUMN a.affiliate_code HEADING "Affiliate Code" FORMAT A3 JUSTIFY LEFT COLUMN b.total_records HEADING 'Total Record Count' FORMAT 09999 JUSTIFY RIGHT
COLUMN c.add_records HEADING 'Add Record Count' FORMAT 09999 JUSTIFY RIGHT COLUMN d.update_records HEADING 'Update Record Count' FORMAT 09999 JUSTIFY RIGHT
COLUMN e.filler HEADING 'Filler' FORMAT A649 JUSTIFY LEFT SPOOL c:\test_record.txt
SELECT distinct
a.affiliate_code||','||b.total_records||','||c.add_records||','||d.update_re cords||','||e.filler
FROM
(SELECT DISTINCT AFFILIATE_ID affiliate_Code
      FROM SYS.FPP_DATA_FOR_FECP) a,
(SELECT COUNT(EMPLOYEE_ID)total_records
      FROM SYS.FPP_DATA_FOR_FECP) b,
(SELECT COUNT(RECORD_TYPE)add_records

      FROM SYS.FPP_DATA_FOR_FECP
      WHERE RECORD_TYPE = 'A') c,


    (SELECT COUNT(RECORD_TYPE)update_records

      FROM SYS.FPP_DATA_FOR_FECP
      WHERE RECORD_TYPE = 'U') d,


    (SELECT FILLER filler

       FROM SYS.FPP_DATA_FOR_FECP) e;

SPOOL off

End Query Received on Mon Apr 23 2001 - 09:54:25 CDT