Re: Formatting columns in a mixed query

,Neil <npluckne_at_ford.com> wrote:
: "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.

Believe your SQL*Plus column commands are incorrect. For example you do not specify a column or alias as a.affiliate_code in your query ie try affiliate_code NOT a.affiliate_code instead.

HTH Helen Received on Tue Apr 24 2001 - 08:48:21 CDT