topic Re: Multiple Table Extract Question in Operating System - HP-UX

Multiple Table Extract Question

Edgar_10 — Thu, 28 Aug 2003 15:16:09 GMT

Hi All,

I have a DB which has daily monthly tables(ie.20030101_data,20030102_data,20030103_data ..,20030131_data).These tables are not partitioned by the way. My question is, how can i query all daily tables of a specific month for a certain pattern (ie.select * from 20030101where NAME='JOHN') without having to write 31 sql statements. And if I wanted to export the returned rows, how can I export in a CSV format.

Your assistance is most appreciated!

Regards

Re: Multiple Table Extract Question

Jean-Luc Oudart — Thu, 28 Aug 2003 15:34:01 GMT

If I understand all tables have same structure. Therefore you can create a view on all these tables (Union).

But if you have lots of tables this will be very costly !

JL

Re: Multiple Table Extract Question

Edgar_10 — Thu, 28 Aug 2003 15:49:25 GMT

Hi J,

You are correct, the tables have the exact same structure,just different names. Any idea how the syntax of the inner/outer/cartesian join should be?

Thanks!

Re: Multiple Table Extract Question

Jean-Luc Oudart — Thu, 28 Aug 2003 16:00:34 GMT

Would be something like :
create view my_view as
( select A,B,C from tab1
union all
select A,B,C from tab2
union all
select A,B,C from tab3
)
But as I said performance will degrade seriously with the number (and size) of tables you include in your view definition

Hopefully NAME is an index in all these tables.

I suppose this was the "old" way to simulate partioning.

Rgds,
Jean-Luc

Re: Multiple Table Extract Question

Edgar_10 — Thu, 28 Aug 2003 16:16:11 GMT

Hi JL,

Yeah, performance wise there will be degradation,but hopefully not too serious.Thanks for the input, appreciate it!

Thanks!

Re: Multiple Table Extract Question

Radim Jarosek — Thu, 28 Aug 2003 18:24:35 GMT

Hi,

concerning export ...

Folowing query return data in CSV format:

select col1 ||','|| col2 ||','|| col3 from
table

If you have oracle, use UTL_FILE package to save output to txt-file.

Example : http://oracle.ittoolbox.com/code/d.asp?d=2012&a=s

HTH

Radim

Re: Multiple Table Extract Question

Indira Aramandla — Fri, 29 Aug 2003 07:00:58 GMT

What version of oracle are you using. If the
version is oracle 9i, then you could use the full functionality of outer join / inner join.

If the version is other than 9i then I guess you use the UNION join. It does degrade performance, so you can write the scrpt and cron the job when there is low load on the system.

When you extract you can spool the output as delimited by comma or space into a text file. thne you can save the file as exel or any other type.

Re: Multiple Table Extract Question

vasundhara — Fri, 29 Aug 2003 11:02:57 GMT

Hi,

You can even go for a PL/SQL procedure or shell script. U just need to pass the date as argument for the query.

I am giving u a small shell script. I think you have to modify this if your single digit day is represneted as 01, 02, 03 etc... If it is represented as 0,1,2... it will work fine.

$i=0
while [[ $i -lt 30 ]]
do
sqlplus -s $1 <select * from 200301$i_data;
quit
ABC
i=`expr $i+1`
done

Regards
VJ.

Re: Multiple Table Extract Question

H.Merijn Brand (procura — Fri, 29 Aug 2003 12:30:36 GMT

Why not use a perl script with dynamic statements and DBD::CVS as backend?

my $dbh = DBI->connect (...);

foreach my $d (20030101 .. 20030131) {
my $sth = $dbh->prepare ("select * from ${d}_data");
$sth->execute;
while (my @row = $sth->fetchrow_array) {
# ... do something with your data
}
}

# and now use DBD::CSV (together with Text::CSV_XS for speed) to write the gathered data

Enjoy, have FUN! H.Merijn