1. 'cuz i was trying just the select statement first to make sure i was
getting the results i wanted, then added the insert part. the bind
variables got left.
2., 3. i was tired and not thinking???
ok so i took the bind variables away and the fetch, the script now runs
without errors.
it's these stupid errors that keep me up at night. thanks
r
"Reidy, Ron"
<[EMAIL PROTECTED]
iopharma.com> To
<[EMAIL PROTECTED]>,
<[EMAIL PROTECTED]>
2004-05-03 17:16 cc
Subject
RE: insert with sub select
1. Where are the bind vars in the SQL statement?
2. Maybe your prepare() call failed?
3. Do you mean to say '$plist[$xx]', not '@plist[$xx]'?
-----------------
Ron Reidy
Senior DBA
Array BioPharma, Inc.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, May 03, 2004 9:11 AM
To: [EMAIL PROTECTED]
Subject: insert with sub select
i'm using dbi to connect to an oracle database
i have a partitioned table and am looping thru this partition name list.
from this list i want to select the data and insert it into a table so i
have this code
for ( $xx=0;$xx<$v_cnt;$xx++)
{[
$prtsql= $dbh->prepare(
"insert into mth_nft
(accountid
, transactioncategory
, price
, starttime)
(select
accountid
,transactioncategory
,price
,starttime
from owner.transaction partition (@plist[$xx])
where
transactioncategory in (2,3,5,6)
and starttime between
p_dates.month_begin(sysdate,-$nummnths)
and p_dates.month_end(sysdate,-$nummnths)
and accountid = 1406240)");
$pigmonkey = $prtsql->execute or die "Error";
print "WOW, $pigmonkey\n";
$prtsql->bind_columns(undef, \$acct, \$transc, \$price, \$startt);
while ( $prtsql->fetch() ) {
print "$acct $transc $price $startt\n";
}
$dbh->commit;
}
when i try to run it there are sometimes no data returned from the select
statement so i get this error
Statement has no result columns to bind (perhaps you need to successfully
call execute first) at ./partfind line 134.
i'm guessing i'm not doing the insert quite right. i know the actual sql
code is correct so my question is how do i write this correctly, and, if
the select part of the query returns no rows, i just want the loop to
continue.
thanks.
r
This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information is
intended
to be for the use of the individual or entity named above. If you are not
the
intended recipient, please be aware that any disclosure, copying,
distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.
