title: Shell or PERL script - run multiple queries based on current active table
body: <p>I am hoping someone can help me with my shell script. I have a file that lists different table prefixes. </p>
  
  <p>The file looks like below:</p>
  
  <pre><code>cat table_names
  table:1234:nfl
  table1:2345:nhl
  table2:5555:nba
  table3:3355:mls
  table4:2463:echl
  </code></pre>
  
  <p>i also have a different table for each day of the week.</p>
  
  <pre><code>daya
  dayb
  dayc
  dayd
  daye
  dayf
  dayg
  dayh
  </code></pre>
  
  <p>I have another file that tells me what the current table is for today</p>
  
  <pre><code>cat today_table
  Today's active table is dayf
  </code></pre>
  
  <p>What I am trying to do is query each table prefix (table - table4) using the current active table for that day.</p>
  
  <p>Here is what I have so far:</p>
  
  <pre><code>#!/bin/ksh
  
  active_table=$(today_table|awk '{print $5}')
  prefix_table=$(cat table_names|grep -v ^#|awk -F":" '{print $1}'|sort -u)
  today=`date "+%Y-%m-%d"`
  OutPutDir="/tneal01/SPOOL"
  OutPutFile="error"
  
  
  
  Execsqlstatement1="select
  to_char(current_date,'YYYYMMDD')  day,
  count(*)                      Total
  from ${prefix_table}.${active_table}
  GROUP BY to_char(current_date,'YYYYMMDD')
  
  
  #adding connection details here
  
  $ORACLE_HOME/bin/sqlplus *********** &lt;&lt; Eossql
      set lines 80
      set pages 50000
      set timing on
      spool tneal01/SPOOL/counts.`date +%Y-%m-%d`.tmp
      $Execsqlstatement1
      /
      spool off
      quit;
  Eossql
  grep "ORA-" $OutPutDir/$OutPutFile.$today.tmp
  if [ $? -eq 0 ]
  then
      echo "LOG MESSAGE sql select failed"
  
  
  exit 0
  </code></pre>
  
  <p>The part am having issues with is the loop. I pretty much what the script to look at the current "active_table" and run the query on all five tables and spool the results to a file</p>
  
  <p>example would be:</p>
  
  <p>today's active table = dayg</p>
  
  <pre><code>select ***
  from table.dayg
  where .....
  
  select ***
  from table1.dayg
  where .....
  
  select ***
  from table2.dayg
  where .....
  
  select ***
  from table3.dayg
  where ....
  
  select ***
  from table4.dayg
  where .....
  </code></pre>
  
  <p>so then the spool file would look like</p>
  
  <pre><code>dayf table
  Monday July 8th
  table  count =31
  table1 count =44
  table2 count =33
  table3 count =55
  table4 count =23
  </code></pre>
  
  <p>Hopefully I explained this clearly. </p>
  
  <p>EDIT: I can write this in Perl or shell. Both suggestions would be greatly appreciated </p>
  
  <p>Thanks for reading!</p>