Re: missing records while running a query using DBI

[alias . abi]

Hi Lan,

Thank you for your reply.

I have tried the query by changed placeholder


$query = qq{
            SELECT item.*
            FROM tmp_loadplans loadplan, tmp_loadplan_items item
            WHERE loadplan.nreference_code = ?
            AND loadplan.id = item.tmp_loadplans_id
        };

my $sth = $dbh->prepare( $query )
    or die "Can't prepare statement: $DBI::errstr";
undef $query;

my $rc = $sth->execute($cReference) or die "Can't prepare statement:
$DBI::errstr";
print "\n Total result set\n";
print $tot;


# Print the result
for (my $iIter = 0; $iIter < $rc; $iIter++)
{
    ($nReference, $cHold, $nPieces, $nWeight,
     $nCube, $cShipper, $cWarehouseLocation,
     $nHazardous, $cDestination, $cComments,
     $nPosition, $cCargoDescription, $cCargoDescription2,
$cShipperReference,
     $cCommunicationReference) = $sth->fetchrow_array;


        .....
        .....
        .......
   $nTotalPieces = $nTotalPieces + $nPieces;
    $nTotalWeight = $nTotalWeight + $nWeight;
    $nTotalCube   = $nTotalCube   + $nCube;




} undef $rc;



But still here I am getting only 14 rows!


The following different variations of the query I have tried through
DBI.


1)

SELECT item.nreference, item.chold, item.npieces, item.nweight,
                  item.ncube, item.cshippername,
item.cwarehouselocation,
                  item.chazardous_code, item.cdestination,
item.ccomments,
                  item.nposition, item.ccargodescription,
                  item.ccargodescription2, item.cshipper_ref,
                  item.ccommunication_ref
           FROM tmp_loadplans loadplan, tmp_loadplan_items item
           WHERE loadplan.nreference_code = 'NYCWHAM30011258'
           AND loadplan.id = item.tmp_loadplans_id

Total result set :17 (Through  DBI)

2)

If I remove these 3 items i.e
item.cshipper_ref,item.ccommunication_ref,item.ccargodescription2,
then , "Total result set :25"

SELECT item.nreference, item.chold, item.npieces, item.nweight,
                  item.ncube, item.cshippername,
item.cwarehouselocation,
                  item.chazardous_code, item.cdestination,
item.ccomments,
                  item.nposition, item.ccargodescription
          FROM tmp_loadplans loadplan, tmp_loadplan_items item
           WHERE loadplan.nreference_code = 'NYCWHAM30011258'
           AND loadplan.id = item.tmp_loadplans_id

3)
If I keep item.cshipper_ref with the query then , "Total result set :
23"

SELECT item.nreference, item.chold, item.npieces, item.nweight,
                  item.ncube, item.cshippername,
item.cwarehouselocation,
                  item.chazardous_code, item.cdestination,
item.ccomments,
                  item.nposition,
item.ccargodescription,item.cshipper_ref
          FROM tmp_loadplans loadplan, tmp_loadplan_items item
           WHERE loadplan.nreference_code = 'NYCWHAM30011258'
           AND loadplan.id = item.tmp_loadplans_id



But if I run the query through Oracle terminal, then all of the abouve
query gave me 25 result.




Version of DBI : 1.53
SQL*Plus: Release 3.3.4.0.0


Once again thanks in advance.