Prev: Using File::Find to delete files and directories older than 30 days
Next: Net::FTP and IBM TSO :-(
From: raj9999 on 10 Mar 2006 15:14
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(a)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();

From: xhoster on 10 Mar 2006 19:35
"raj9999" <rajpanchal(a)gmail.com> wrote:
> 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(a)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)) {
#....
};

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB
From: robic0 on 10 Mar 2006 20:15
On 10 Mar 2006 12:14:53 -0800, "raj9999" <rajpanchal(a)gmail.com> wrote:

>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(a)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.
From: Mark Clements on 11 Mar 2006 05:48
robic0 wrote:
> On 10 Mar 2006 12:14:53 -0800, "raj9999" <rajpanchal(a)gmail.com> wrote:
>
>> 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(a)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
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
From: robic0 on 14 Mar 2006 20:32
On Sat, 11 Mar 2006 11:48:39 +0100, Mark Clements <mark.clementsREMOVETHIS(a)wanadoo.fr> wrote:

>robic0 wrote:
>> On 10 Mar 2006 12:14:53 -0800, "raj9999" <rajpanchal(a)gmail.com> wrote:
>>
>>> 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(a)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
>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

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
 | 
Pages: 1
Prev: Using File::Find to delete files and directories older than 30 days
Next: Net::FTP and IBM TSO :-(