Wrap query in quotes

ssmith001 · #1 (**permalink**) 11-17-06, 17:38

I need to wrap the following query ($qry) in double quotes but I can't seem to be able to figure out how. I keep getting this error: ORA-01756: quoted string not properly terminated (DBD ERROR: OCIStmtPrepare) at promo.pl line 911

Code:

my $qry;
my @new_array;
my $num_days_of_supp = 315;

#print "Days of Supply = $num_days_of_supp\n";

my @months_to_sum = ( 12 - ($num_days_of_supp/30) .. 11 );

if ( $num_days_of_supp % 30 == 0 )   # evenly divisible by 30
{
  $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;

  $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
}

MCrowley · #2 (**permalink**) 11-21-06, 16:30

Try using the qq operator.

ssmith001 · #3 (**permalink**) 11-21-06, 19:27

This is driving me nuts. I've tried qq and I can't get it to work.

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
  };

	LogMsg($qry);
	
 	# prepare and execute the query
 	my $sth_C = $dbh->prepare( $qry ) or die $dbh->errstr;
  																	     
	$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;
}

Here's a section of the output. I keep getting an error: ORA-00972: identifier is too long (DBD ERROR: OCIStmtPrepare) at promo.pl line 931.

Code:

*** Tue Nov 21 16:21:54 2006 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"
EOF    
  };
  
  if ( $num_days_of_supp % 30 != 0 )
  {
  	@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"

MCrowley · #4 (**permalink**) 11-30-06, 17:09

Just for the sake of it, can you have the script print out the value of $qry, just to see if maybe a space was missed somewhere? Identifier too long, sonuds like a table or column name is getting garbled.