Re: Formatting columns in a mixed query

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in <te8no8modso957_at_beta-news.demon.nl>:

>
>"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
>
>
>

Thanks for the advice - I've tried this but I still get the same result only minus the ",". eg:

AFF TOTAL_RECORDS ADD_RECORDS UPDATE_RECORDS F --- ------------- ----------- -------------- - FPP 23 23 0 is the result of removing the ||','|| whereas the output required is:

Affiliate Total Record Add Record Update Record Filler Code, Code, Code, Code,
--------- ------------ ---------- ------------- ------ FPP, 23, 23, 0 (I know I forgot to add the split characters in the COLUMN statements)

Sorry to be pendantic - but it's the format the user area requires & insists on and I can find nothing in my notes to cover this exactly!! The only other way I can see of doing it is to do a SELECT then INSERT or UPDATE into a new table and run the query again on the new table.

Regards,

Neil. Received on Tue Apr 24 2001 - 02:48:00 CDT