Perl DBI - Select across multiple Database

[raj9999]

I want to write a sql where I want to get data from target database and
Source database on using UNION clause. But I dont want to use Oracle's
DBLINK.

Oracle version
---------------------------
select col1, col2 from users
UNION
select col1, col2 from users@sourcedb

I want to write above sql in such a way that I dont have to use
oracle's dblink. Can I do something like follows:

Perl - DBI version
-----------------------------
$target_sql = "select col1, col2 from users <from target db connection
string>
UNION
select col1, col2 from users <from source db connection
string>";

$target_sth = $dbh_ods->prepare( $target_sql );
$target_sth->execute();

 

[xhoster]

I want to write a sql where I want to get data from target database and
Source database on using UNION clause. But I dont want to use Oracle's
DBLINK.

Oracle version
---------------------------
select col1, col2 from users
UNION
select col1, col2 from users@sourcedb

I want to write above sql in such a way that I dont have to use
oracle's dblink. Can I do something like follows:

Perl - DBI version
-----------------------------
$target_sql = "select col1, col2 from users <from target db connection
string>
UNION
select col1, col2 from users <from source db connection
string>";

$target_sth = $dbh_ods->prepare( $target_sql );
$target_sth->execute();

No.

But you could probably do something like:

my $sth1=$dbh1->prepare("--something");
my $sth2=$dbh2->prepare("--something");
$sth1->execute;
$sth2->execute;
while ($_ = ($sth1->fetch or $sth2->fetch)) {
#....
};

 

[robic0]

I want to write a sql where I want to get data from target database and
Source database on using UNION clause. But I dont want to use Oracle's
DBLINK.

Oracle version
---------------------------
select col1, col2 from users
UNION
select col1, col2 from users@sourcedb

I want to write above sql in such a way that I dont have to use
oracle's dblink. Can I do something like follows:

Perl - DBI version
-----------------------------
$target_sql = "select col1, col2 from users <from target db connection
string>
UNION
select col1, col2 from users <from source db connection
string>";

$target_sth = $dbh_ods->prepare( $target_sql );
$target_sth->execute();

I've been away from database for a while.
What's wrong with the WHERE clause in table's construct?
WHERE table1.col = table2.col
I vaguely remember UNION intersection. Could always merge.

 

[Mark Clements]

I've been away from database for a while.
What's wrong with the WHERE clause in table's construct?
WHERE table1.col = table2.col

He's talking about merging datasets from separate databases. WHERE isn't
an issue here.

I vaguely remember UNION intersection.

union and intersection are distinct set operations. Your sentence is
similar in spirit to saying:

"I remember addition subtraction".

At least it adequately demonstrates your level of understanding.

Mark

 

[robic0]

He's talking about merging datasets from separate databases. WHERE isn't
an issue here.

union and intersection are distinct set operations. Your sentence is
similar in spirit to saying:

"I remember addition subtraction".

At least it adequately demonstrates your level of understanding.

Mark

Seems reasonable UNION does that. The trouble with addition and subtraction
is, if thats all you do all day, its easy to impress somebody, but the job
sure sucks after a while.

I've casually looked over the various odbc modules in DBI. To me it seems
basackward. Why program to odbc directly when there is Mdac, which is just
another DBI module? Thats what I know, the mdac paradigm.

Whenever you have to invoke UNION, a complex sql construct, there is a
better chance for consistent output when the sql is generated and used as
a stored procedure instead of a string used in an execute statement.

In that vein, its kind of funny here how nothing is mentioned as to what form
the db's are in. Is it assumed to be from the same provider? Oracle?
I don't know. What I do know is that its easier to import a table from one db
to another, then do sql for a merge. If the sql's UNION can't be done in the provider,
why try it from DB?

Seems its not so critical then. Unfortunately, I don't get paid for guessing.
And Perl and guessing is the same thing, imho!

rob