Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Open Source > Perl
|
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Perl section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 27th, 2006, 04:28 PM
Registered User
 
Join Date: Nov 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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;
}


 
Old February 23rd, 2007, 10:13 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 1,525
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to crmpicco Send a message via AIM to crmpicco Send a message via MSN to crmpicco Send a message via Yahoo to crmpicco
Default

maybe this would be better posted on an Oracle DB forum? it is Oracle you are using isn't it?

www.crmpicco.co.uk
www.ie7.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
convert a SQL Statement from MS Access to a SQL Corey Access 6 March 28th, 2007 12:33 PM
SQL Statement??? carswelljr SQL Server 2000 2 August 25th, 2006 12:40 PM
build switch statement to make dynamic crmpicco Javascript How-To 0 October 27th, 2005 05:44 AM
T-SQL statement sam78_my SQL Language 1 September 29th, 2003 02:14 AM
Dynamic IF Statement fantom222 VB How-To 7 June 13th, 2003 09:16 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.