Re: Select statement question

==========Ryan A. Cox, 7/30/95========== I need to pull some data out of an Oracle database with SQL. It would greatly simplify my life if, somehow, through the select statement I could insert commas and quotes in such a manner that my spool file would be a comma delimeted format. If this is possible please let me know.

--

• http://www.psyber.com/~rcox

==========Ryan A. Cox, 7/30/95========== I need to pull some data out of an Oracle database with SQL. It would greatly simplify my life if, somehow, through the select statement I could insert commas and quotes in such a manner that my spool file would be a comma delimeted format. If this is possible please let me know.

--

• http://www.psyber.com/~rcox

Of the responses that I have seen, all are using the concatenation ( || ). The limitation with this is that an output column can be a maximum of 256 characters.
An alternative format includes a literal , or " as a column which are enclosed in single quotes. This will also allow you to format each column as you desire. Below is an example ( include the set commands.)

set heading off
set feedback off
set newpage 0
set pagesize 0
set space 0
set linesize xxx (xxx = width of line for one record)

spool xxxxxxxx (xxxxxxxx = output filename)

clear breaks
clear columns
clear computes
btitle off
ttitle off
column acct_nbr format 9999999999
column balance format 99999999.99
column name1 format a15
column name2 like name1

select acct_nbr, ',' , balance, ',"', name1, '","', name2, '",' from table1;

spool off

set heading on (use set commands to put options back to your defaults) set feedback on
set newpage 0
set pagesize 60
set space 1
set linesize xxx (xxx = set back to your default value)

The output will be the following:

1234567890, 850125.89,"John A. Doe ","Jane B. Doe ", 2345678901, 345682.54,"Doug White ","Betty White ",

We have found that a , is usually needed at the end of each record.

Sorry this is so long but hope that it helps. Received on Tue Aug 01 1995 - 00:00:00 CEST