Hello: I have, I believe, a variable interpolation question. I have a perl program that reads in a file that contains sql statments. I pull each SQL and push it into a hash, where the hash is a transaction_number and the associated SQL. The sql statments however hold Perl variables such as: 
   ...
   WHERE asof_date = '$test_date'
   ...

[download]
Of course, $test_date is defined in the program, and my hope is that once the sqls are read in, the variables in the sqls take the approproate value that is assigned to them in the program. I can make the program work fine if I replace the actual string of variable with the variable like this in the loop while I am reading in the file: 
   ...
   s/\$test_date/$test_date/g;
   ...

[download]
But that is not really what I wanted to do. I was hoping that the variable in the sql file is interpolated and takes on the value what is defined in the program. Here is a skelatal of the code: 
#===============================================#
$ss_source_file   = "C:/code/mla/tmp.sql";
$test_date = "30-APR-2011";
$schema    = "DEV";
#####################
%trancd_sql = (); # each SQL in a hash with a 
                  # transaction number and associated SQL

open(SS_SOURCE, "< $ss_source_file ") || die "Can't open SQL source fi
+le! $!\n";
while (<SS_SOURCE>) {
   if (/^;/) {    # the end of each SQL 
      $end_sql   = 1; 
      $begin_sql = 0;
      $trancd_sql{$tran_code} = $sql; ## push each  tran_code/SQL into
+ hash
      $sql = '';
      $tran_code = '';
   }
   
   ## beginning of an sql
   if (/^\s*-{2,}\s*tran_code\s*\d{4,}/) { 
      ($tran_code) = $_ =~ /^\s*-{2,}\s*tran_code\s*(\d{4})/;
      $begin_sql = 1;
      $end_sql   = 0;
   }

   if (($begin_sql == 1) && ($end_sql != 1)) {
      #s/\$test_date/$test_date/g; # works if uncommented
      #s/\$schema/$schema/g;       # works if uncommented
      $sql .= $_;
   }
   
}
close(SS_SOURCE);

$trcd = 1;
while ($trcd > 0) {
   print "Enter Tran code, or '0' to exit: ";
   $trcd = <STDIN>;
   chomp($trcd);
   print $trancd_sql{$trcd};
}   
#====================================================#

[download]
and here is the data file with he sqls in it. 
#=====================================================#
-- tran_code 1752
SELECT *
   FROM $schema.TRANSACTOPMS
      WHERE TRANSACTION_CD IN ('146','147')
      AND AMT < 0
      AND ASOF_DATE = '$test_date'

;
-- tran_code 1753
SELECT *
      FROM $schema.TRAN
      WHERE TRANSACTION_CD IN ('196','197')
      AND ASOF_DATE = '$test_date'
      AND RECORD_VERSION_NBR = '0'
;

-- tran_code 1758
SELECT COUNT(SS_NO), SUM(ABS(SS_PRINCIPAL))     
      FROM $schema.SS_MLA_TRAN
      WHERE INVESTOR_ID = '195'
      AND MAN_CD = '4'
      AND ASOF_DATE = '$test_date'
      AND RECORD_VERSION_NBR = '0'
      and GROUP_ID = 'PCC'
;      

-- tran_code 1765
      ... and so on, you get the idea
;      
#==================================================#

[download]
What I wanted to see as output is: 
SELECT *
   FROM DEV.TRANSACTOPMS
      WHERE TRANSACTION_CD IN ('146','147')
      AND AMT < 0
      AND ASOF_DATE = '30-APR-2011'

[download]
instead of the: 
SELECT *
   FROM $schema.TRANSACTOPMS
      WHERE TRANSACTION_CD IN ('146','147')
      AND AMT < 0
      AND ASOF_DATE = '$test_date'

[download]
As I mentioned before, I can make this work by replacing the actual string (which is a variable name in the sql), but my objective here is getting a more elegant solution and for my own personal learning as well. Any ideas of what I am missing? Greatly appreciate your input!

In reply to Variable interpolation in a file to be read in by tapolyaip

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.