RE: insert with sub select

[Reidy, Ron]

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.