Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Multiple Table Extract Question

SOLVED
Go to solution
Edgar_10
Frequent Advisor

Multiple Table Extract Question

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
8 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: Multiple Table Extract Question

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
fiat lux
Edgar_10
Frequent Advisor

Re: Multiple Table Extract Question

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!
Jean-Luc Oudart
Honored Contributor
Solution

Re: Multiple Table Extract Question

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
fiat lux
Edgar_10
Frequent Advisor

Re: Multiple Table Extract Question

Hi JL,

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

Thanks!
Radim Jarosek
Regular Advisor

Re: Multiple Table Extract Question

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
Indira Aramandla
Honored Contributor

Re: Multiple Table Extract Question

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.

Never give up, Keep Trying
vasundhara
Frequent Advisor

Re: Multiple Table Extract Question

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.

H.Merijn Brand (procura
Honored Contributor

Re: Multiple Table Extract Question

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
Enjoy, Have FUN! H.Merijn