Re: Formatting columns in a mixed query

"Neil" <npluckne_at_ford.com> wrote in message news:Xns908CA1EC0856824361267ford_at_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

Yeah you are concatenating everthing, so you get only *1* column back. Remove those || replacing them by ,
and retry

Hth,

Sybrand Bakker, Oracle DBA Received on Mon Apr 23 2001 - 11:59:52 CDT