From: "Sybrand Bakker" Newsgroups: comp.databases.oracle.misc Subject: Re: Formatting columns in a mixed query Date: Mon, 23 Apr 2001 18:59:52 +0200 Message-ID: References: X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Complaints-To: abuse@nl.demon.net NNTP-Posting-Host: sybrandb.demon.nl X-NNTP-Posting-Host: sybrandb.demon.nl:212.238.21.78 X-Trace: beta-news.demon.nl 988045064 beta-news:57738 NO-IDENT sybrandb.demon.nl:212.238.21.78 Lines: 57 "Neil" wrote in message news: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 Yeah you are concatenating everthing, so you get only *1* column back. Remove those || replacing them by , and retry Hth, Sybrand Bakker, Oracle DBA