Help w/ dynamic SQL statement
I'm hoping someone here can help me as no other forum I've posted to has been able to. Here's the scoop...I have a subroutine that is supposed to create a SQL statement on the fly. The resulting query looks like this when it gets sent to the prepare statement:
"SELECT GREATEST(ROUND((SUM(mth_01 + mth_02 + mth_03 + mth_04 + mth_05 + mth_06 + mth_07 + mth_08 + mth_09 + mth_10 + mth_11 + mth_12) / 365 ) * 120 ), ROUND (SUM (mth_08 + mth_09 + mth_10 + mth_11))) QTY, b.p_dlrnet, b.p_dlrsplrcode
FROM pac.historyreportmonthlybysku a
, stsc.sku b
WHERE a.loc = ?
AND a.dmdunit = ?
AND a.loc = b.loc
AND a.dmdunit = b.ITEM
GROUP BY a.loc
, a.dmdunit
, b.p_dlrnet
, b.p_dlrsplrcode"
I am getting this error: ORA-00972: identifier is too long (DBD ERROR: OCIStmtPrepare) at promo.pl line 944.
I've checked and there are no field or table names over 30 chars in length, so I can't for the life of me figure out what the problem is. I can cut and paste this query into my SQL editor and it runs with no problem. Here is the actual Perl code:
sub get_BCE_qty
{
my $num_days_of_supp = shift;
my $loc = shift;
my $dmdunit = shift;
LogMsg("Days of Supply = $num_days_of_supp");
my $qry;
my @new_array;
my @months_to_sum = ( 12 - ($num_days_of_supp/30) .. 11 );
if ( $num_days_of_supp % 30 == 0 )
{
$qry = '"SELECT GREATEST(ROUND((SUM('
. join( ' + ', map { sprintf "mth_%02i", $_ } ( 1 .. 12 ) )
. ") / 365 ) * $num_days_of_supp ), ROUND (SUM ("
. join(' + ', map { sprintf "mth_%02i", $_ } @months_to_sum)
. '))) QTY, b.p_dlrnet, b.p_dlrsplrcode '
. <<'EOF';
FROM pac.historyreportmonthlybysku a
, stsc.sku b
WHERE a.loc = ?
AND a.dmdunit = ?
AND a.loc = b.loc
AND a.dmdunit = b.ITEM
GROUP BY a.loc
, a.dmdunit
, b.p_dlrnet
, b.p_dlrsplrcode"
EOF
}
else
{
@new_array = @months_to_sum;
shift @new_array; # remove the 1st element since this is the one we divide by 2
$qry = '"SELECT GREATEST(ROUND((SUM('
. join( ' + ', map { sprintf "mth_%02i", $_ } ( 1 .. 12 ) )
. ") / 365 ) * $num_days_of_supp ), ROUND (SUM ("
. sprintf ("mth_%02i", $months_to_sum[0]) . " / 2 + "
. join(' + ', map { sprintf "mth_%02i", $_ } @new_array)
. '))) QTY, b.p_dlrnet, b.p_dlrsplrcode '
. <<'EOF';
FROM pac.historyreportmonthlybysku a
, stsc.sku b
WHERE a.loc = ?
AND a.dmdunit = ?
AND a.loc = b.loc
AND a.dmdunit = b.ITEM
GROUP BY a.loc
, a.dmdunit
, b.p_dlrnet
, b.p_dlrsplrcode"
EOF
};
print "\n\n$qry\n\n";
# prepare and execute the query
my $sth_C = $dbh->prepare( $qry ) or die $dbh->errstr;
#LogMsg("$loc $dmdunit");
$sth_C->execute( $loc, $dmdunit );
my ($col1, $col2, $col3);
$sth_C->bind_col(1, \$col1);
$sth_C->bind_col(2, \$col2);
$sth_C->bind_col(3, \$col3);
$sth_C->fetch();
my $BCE_qty = $col1;
my $dlrnet = $col2;
my $dlrsupcode = $col3;
return $BCE_qty, $dlrnet, $dlrsupcode;
}
|
