- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Multiple Table Extract Question
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-28-2003 08:16 AM
тАО08-28-2003 08:16 AM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-28-2003 08:34 AM
тАО08-28-2003 08:34 AM
Re: Multiple Table Extract Question
But if you have lots of tables this will be very costly !
JL
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-28-2003 08:49 AM
тАО08-28-2003 08:49 AM
Re: Multiple Table Extract Question
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-28-2003 09:00 AM
тАО08-28-2003 09:00 AM
Solutioncreate 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-28-2003 09:16 AM
тАО08-28-2003 09:16 AM
Re: Multiple Table Extract Question
Yeah, performance wise there will be degradation,but hopefully not too serious.Thanks for the input, appreciate it!
Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-28-2003 11:24 AM
тАО08-28-2003 11:24 AM
Re: Multiple Table Extract Question
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-29-2003 12:00 AM
тАО08-29-2003 12:00 AM
Re: Multiple Table Extract Question
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-29-2003 04:02 AM
тАО08-29-2003 04:02 AM
Re: Multiple Table Extract Question
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 <
quit
ABC
i=`expr $i+1`
done
Regards
VJ.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-29-2003 05:30 AM
тАО08-29-2003 05:30 AM
Re: Multiple Table Extract Question
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