Home » SQL & PL/SQL » SQL & PL/SQL » Re: column names on first line only
Re: column names on first line only [message #18341] Mon, 28 January 2002 15:15
gr
Messages: 5
Registered: January 2002
Junior Member
The following example will produce a file with a heading on the first line followed by the data that is selected. I hope that it helps.

spool C:INVOICE.TXT
SET PAGESIZE 0
SET NEWPAGE NONE
SET LINESIZE 463
SET FEEDBACK OFF
SELECT '"billing period","eligibility_ud","last_name","first_name","middle_name","location","cbr","Covg1","eff_date","medical premium"'
FROM DUAL;
SELECT chr(34)||SUBSTR(TO_CHAR(TO_DATE(DUE_DATE,'DD-MM-RR'),'MM/DD/YYYY'),7,4)||
SUBSTR(TO_CHAR(TO_DATE(DUE_DATE,'DD-MM-RR'),'MM/DD/YYYY'),1,2)||chr(34)||chr(44)||
CHR(34)||SSN||CHR(34)||chr(44)||
(CHR(34)||SUBSTR(RTRIM(ENROLLEE),1,INSTR(ENROLLEE,',',1,1)-1)||chr(34))||chr(44)||
CHR(34)||SUBSTR(SUBSTR(ENROLLEE,1,INSTR(ENROLLEE,' ',1,1)-1),INSTR(ENROLLEE,',',1,1)+1)
||chr(34)||chr(44)||
CHR(34)||substr(enrollee,length(enrollee),1)||chr(34)||chr(44)||
CHR(34)||LOCATION||chr(34)||chr(44)||
CHR(34)||' '||chr(34)||chr(44)||
CHR(34)||LOB||COV_STAT||chr(34)||chr(44)||
CHR(34)||LTRIM(SUBSTR(TO_CHAR(TO_DATE(EFF_DATE,'DD-MM-RR'),'MM/DD/YYYY'),1,2),'0')||'/'||
LTRIM(SUBSTR(TO_CHAR(TO_DATE(EFF_DATE,'DD-MM-RR'),'MM/DD/YYYY'),4,2),'0')||'/'||
SUBSTR(TO_CHAR(TO_DATE(EFF_DATE,'DD-MM-RR'),'MM/DD/YYYY'),9,2)||chr(34)||chr(44)||
CHR(34)||'$'||PREMIUM_AMT||chr(34)
FROM INVOICE_PRINT
WHERE GRP_NUMBER = '1224';
spool off;
Previous Topic: not a vaild month error
Next Topic: Re: PLS-00201: identifier 'BRANCH.BRANCH' must be declared
Goto Forum:
  


Current Time: Tue Jul 07 05:28:54 CDT 2020