RE: Building dynamic sql that will do CSV

[Jamadagni, Rajendra]

set colsep ','

 

might help too.

 

Raj

--------------------------------------------------------------------------------
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
From: Michael Kline [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 28, 2003 11:50 AM
To: Multiple recipients of list ORACLE-L
Subject: Building dynamic sql that will do CSV

I'm trying to build something like this:

 

select 'select '''||TABLE_NAME||','||COLUMN_NAME||''', ''',''',  min('||COLUMN_NAME||'),
 max('||COLUMN_NAME||') from  '
        ||OWNER||'.'||TABLE_NAME||' where '||COLUMN_NAME||' is not null;'
        FROM dba_tab_columns
        WHERE table_name in ('PAYMENTS_RECEIVED','ADDRESS_VAL','BATCH_CONTROL',
          'CUSTOMER','CUSTOMER_DEFAULT','EXPLODED_PRODUCT','EXPLODED_PUBLICATION',
          'EXPLOSION','ORDERS_MERCH','INVOICE','INVOICE_MERCH','CLIENT_SAMPLES','INVENTORY')
        AND OWNER = 'HIST'
 order by owner, table_name, column_id;

which gives me

select 'CLIENT_SAMPLES,SAMPLE_DATE5', '
',  min(SAMPLE_DATE5),
        max(SAMPLE_DATE5) from  HIST.CLIENT_SAMPLES where SAMPLE_DATE5 is not null;

 

and an output of:

CLIENT_SAMPLES,SAMPLE_DATE5   0                11/09/2002 09:38

 

What I want is a "comma" between everything and perhaps ", but building this dynamically has got my brain all fuddled. Trying to figure out what to build in the dynamic to get the executed sql to look right... I just can't seem to get the right combination...

 

I'd like something like:

CLIENT_SAMPLES,SAMPLE_DATE5,   0                ,11/09/2002 09:38

or

"CLIENT_SAMPLES","SAMPLE_DATE5","0                ","11/09/2002 09:38"

 

Michael Alan Kline, Sr.
Principal Consultant
Business to Business Solutions, LLC
Phone: 804-744-1545  Cell: 804-314-6262
ICQ: 1009605, 975313
Email: [EMAIL PROTECTED] Web: www.b2bsol.com

 

********************************************************************This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*********************************************************************2