Hi there can anyone tell me how I

[eflavin]

Hi there
can anyone tell me how I would add column headers for each of the fields to the report file created from this statement.

SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET LINESIZE 500
SEt TRIMSPOOL ON

SPOOL C:\SPOOLED_OUT.csv
SELECT '"'||ENET_ST_SALES.CUSTOMER_ID||'","'||ENET_ST_SALES.CUST_NO||'","'||
ENET_ST_SALES.SALES_ID||'","'||ENET_ST_SALES.PRODUCT_ID||'","'||ENET_ST_SALES.PRODUCT_NAME||'","'||
ENET_ST_SALES.LINE_SPEED||'"'
FROM DM_STAGE_USER.ENET_ST_SALES
WHERE ( (ENET_ST_SALES.PRODUCT_ID = 1899) AND (ENET_ST_SALES.LINE_SPEED IS
NULL) );
SPOOL OFF

 

[Kenrae]

You can try this:

SPOOL C:\SPOOLED_OUT.csv

SELECT 'CUSTOMER_ID   CUST_NO    SALES_ID    PRODUCT_ID    PRODUCT_NAME    LINE_SPEED'
FROM DUAL;

SELECT '"'||ENET_ST_SALES.CUSTOMER_ID||'","'||ENET_ST_SALES.CUST_NO||'","'||
ENET_ST_SALES.SALES_ID||'","'||ENET_ST_SALES.PRODUCT_ID||'","'||ENET_ST_SALES.PRODUCT_NAME||'","'||
ENET_ST_SALES.LINE_SPEED||'"' 
FROM DM_STAGE_USER.ENET_ST_SALES 
WHERE ( (ENET_ST_SALES.PRODUCT_ID = 1899) AND (ENET_ST_SALES.LINE_SPEED IS
NULL) );
SPOOL OFF

 

[sem]

To get the output to be more sql*plus-like you should use column headings:

column a heading "CUSTOMER_ID CUST_NO SALES_ID PRODUCT_ID PRODUCT_NAME LINE_SPEED"

SELECT '"'||ENET_ST_SALES.CUSTOMER_ID||'","'||ENET_ST_SALES.CUST_NO||'","'||
ENET_ST_SALES.SALES_ID||'","'||ENET_ST_SALES.PRODUCT_ID||'","'||ENET_ST_SALES.PRODUCT_NAME||'","'||
ENET_ST_SALES.LINE_SPEED||'"' a
FROM DM_STAGE_USER.ENET_ST_SALES
WHERE ( (ENET_ST_SALES.PRODUCT_ID = 1899) AND (ENET_ST_SALES.LINE_SPEED IS
NULL) );

Of course you have to calculate the number of spacings between words according to column datatypes.

You may also not to concatenate all columns but rather quote them separately. The result will contain extra spacings but will be better formatted. In this case your script may be:

column CUSTOMER_ID heading CUSTOMER_ID
column CUST_NO heading CUST_NO
...

/*this is for unheading commas*/
column b heading ""
column a heading ""
...

SELECT '"'||ENET_ST_SALES.CUSTOMER_ID||'"' CUSTOMER_ID,
',' a,
'"'||ENET_ST_SALES.CUST_NO||'"' CUST_NO,
',' b,
...