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.PrincipalConsultantBusiness to Business Solutions, LLCPhone: 804-744-1545 Cell: 804-314-6262ICQ: 1009605, 975313Email: [EMAIL PROTECTED] Web: www.b2bsol.com
RE: Building dynamic sql that will do CSV
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 AMTo: Multiple recipients of list ORACLE-LSubject: 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.PrincipalConsultantBusiness to Business Solutions, LLCPhone: 804-744-1545 Cell: 804-314-6262ICQ: 1009605, 975313Email: [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
Antw: Building dynamic sql that will do CSV
Hi Michael, are you looking for something like: SET COLSEP ','? Greetings, Guido [EMAIL PROTECTED] 28.08.2003 17.49 Uhr 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','INVEN TORY') 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 011/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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Building dynamic sql that will do CSV
see http://www.cybcon.com/~jkstill/util/dump/dump.html On Thu, 2003-08-28 at 08:49, Michael Kline wrote: 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','INVEN TORY') 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 011/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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).