Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Wrap query in quotes

    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
    }

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Try using the qq operator.

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    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"

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  5. #5
    Join Date
    Dec 2006
    Posts
    6
    I might be blind, but where is the matching double-quote character for the last term in your SQL?

    Code:
    b.p_dlrsplrcode"
    And I agree with the previous responder, can you print the generated query as a sanity check?

    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •