cjacksonjr has asked for the wisdom of the Perl Monks concerning the following question:

Below is a program which tells the user what # of alarms occurred in the first table (notifLogTable) below (total, those <2 secs., those >=2 and <10 secs., and those >10 secs). Now I need to Use info from Table 2 (rhic_au_fy_01) to eliminate alarms that started (AlarmStart)w/in 10 secs of a Quench (ev-yquench or ev-bquench) and I'm having trouble. 

notifLogTable:
      AlarmStart      AlarmStop            UTimeStart UTimeStop   Site
+WideName
 Oct  1 2001  3:11PM, Oct  1 2001  3:11PM, 1001963508,1001963508,bo11-
+qd1-ps.watch : diffRefCurrentM
 Oct  1 2001  3:11PM, Oct  1 2001  3:11PM, 1001963510,1001963511,bo11-
+qd1-ps.watch : diffRefCurrentM
 Oct  1 2001  3:12PM, Oct  1 2001  3:12PM, 1001963548,1001963549,bo11-
+qd1-ps.watch : diffRefCurrentM
 Oct  1 2001  3:13PM, Oct  1 2001  3:13PM, 1001963600,1001963600,bo11-
+qd1-ps.watch : diffRefCurrentM
 Oct  1 2001  3:13PM, Oct  1 2001  3:13PM, 1001963612,1001963612,bo11-
+qd1-ps.watch : diffRefCurrentM
 Oct  1 2001  3:29PM, Oct  1 2001  3:40PM, 1001964598,1001965206,b2-q8
+9-ps.watch : diffRefWfgM
 Oct  1 2001  3:29PM, Oct  1 2001  3:40PM, 1001964598,1001965206,b6-q8
+9-ps.watch : diffRefWfgM
 Oct  1 2001  3:29PM, Oct  1 2001  3:50PM, 1001964598,1001965848,b2-q8
+9-ps.watch : diffRefCurrentM
 Oct  1 2001  3:29PM, Oct  1 2001  3:51PM, 1001964598,1001965874,b6-q8
+9-ps.watch : diffRefCurrentM
 Oct  1 2001  3:30PM, Oct  1 2001  3:30PM, 1001964600,1001964658,bo2-t
+h4-ps.watch : diffRefWfgM
 Oct  1 2001  3:30PM, Oct  1 2001  3:30PM, 1001964601,1001964612,bi4-t
+v4-ps.watch : diffRefWfgM
 Oct  1 2001  3:30PM, Oct  1 2001  3:30PM, 1001964601,1001964628,bo10-
+th4-ps.watch : diffRefWfgM
 Oct  1 2001  3:30PM, Oct  1 2001  3:30PM, 1001964601,1001964656,bi5-t
+h5-ps.watch : diffRefWfgM
 Oct  1 2001  3:30PM, Oct  1 2001  3:30PM, 1001964602,1001964603,bi12-
+tv4-ps.watch : diffRefWfgM

rhic_au_fy_01 Table:
rhicTimeUS        rhichTime            fillNo  event
1001964596.91503, Oct  1 2001  3:29PM, 1154, ev-bquench 
1001966883.51251, Oct  1 2001  4:08PM, 1155, ev-bquench 
1002000082.0685, Oct  2 2001  1:21AM, 1159, ev-bquench 
1002036510.31338, Oct  2 2001 11:28AM, 1166, ev-yquench 
1002042610.70097, Oct  2 2001  1:10PM, 1167, ev-bquench 
1002068439.82812, Oct  2 2001  8:20PM, 1172, ev-bquench 
1002105172.71504, Oct  3 2001  6:32AM, 1180, ev-bquench 
1002105172.72965, Oct  3 2001  6:32AM, 1180, ev-yquench 
1002121768.47786, Oct  3 2001 11:09AM, 1181, ev-bquench 
1002121905.31127, Oct  3 2001 11:11AM, 1181, ev-yquench 
1002145148.25541, Oct  3 2001  5:39PM, 1184, ev-yquench 
1002145915.95759, Oct  3 2001  5:51PM, 1184, ev-bquench 
1002161018.07564, Oct  3 2001 10:03PM, 1184, ev-bquench 
1002161852.53742, Oct  3 2001 10:17PM, 1184, ev-bquench 
1002162711.54149, Oct  3 2001 10:31PM, 1184, ev-bquench 



#! /usr/local/bin/perl -w
# A perl program to analyze the notifAlarmLog Table through quench tim
+es

use FileHandle;
use IPC::Open2;
use Symbol;
# use Tk;
use Sybase::CTlib;
use Time::Local;
use strict;
my(%systemNames);
systemNames(); # fill in the hash
# do some analysis of the notifAlarmLog Db
use strict;
my $dbEvents = new Sybase::CTlib 'harmless','harmless','OPSYB1','fille
+ventsT';
$dbEvents->ct_sql("use rhic_au_fy01_fill");
my $sql = "SELECT *
           FROM fillEventsT
           WHERE rhicTime like 'Oct%' and (event like 'ev-bquench' or 
+event like 'ev-yquench')";
my(@fills,$fill);
@fills =  $dbEvents->ct_sql($sql);
foreach $fill (@fills) {
   print "$fill->[0], $fill->[1], $fill->[2], $fill->[3] \n";
}
my $dbAlarms = new Sybase::CTlib 'harmless','harmless','OPSYB1','notif
+LogTable';
$dbAlarms->ct_sql("use TomTest");
my $sql = "SELECT distinct name FROM notifAlarmLog";
my(@rows,$row);
@rows =  $dbAlarms->ct_sql($sql);
foreach $row (@rows) {
  $sql = "SELECT * FROM notifAlarmLog WHERE name like '$row->[0]'";
  my($supply,@supplies);
  @supplies = $dbAlarms->ct_sql($sql);
  # count all alarms
  my $count = @supplies;
  # how many alarms are 2 seconds or less?
  my $OneSecCount = 0;
  # how many alarms are >2 and <10 seconds?
  my $TwoSecCount = 0;
  # how many alarms are >10 seconds?
  my $TenSecCount = 0;
  # how many alarms occur w/in 10 seconds of a QUENCH ENVENT?

  foreach $supply (@supplies) {
    $OneSecCount++  if(($supply->[4]-$supply->[3]) <= 2);
    $TwoSecCount++  if(($supply->[4]-$supply->[3]) > 2 and ($supply->[
+4]-$supply->[3]) <= 10);
    $TenSecCount++  if(($supply->[4]-$supply->[3]) >10);
  }
  my ($f1,$adoName,$f3) = split(":",$row->[0]);
  my $SiteWideName = $main::systemNames{$adoName}  . ":$f3";
  # print ":$adoName: :$SiteWideName: \n";
   print "$SiteWideName , $count , $OneSecCount , $TwoSecCount , $TenS
+ecCount \n";
}

sub systemNames {
  # fill in the systenName hash
  use strict;
  my $dbAdo = new Sybase::CTlib 'harmless','harmless','OPSYB1','notifL
+ogTable';
  $dbAdo->ct_sql("use serverAdo");
  my $sql = "SELECT name,systemName from adoInst where name like 'psWa
+tch%'";
  my(@rows,$row);
  @rows =  $dbAdo->ct_sql($sql);
  foreach $row (@rows) {
    $main::systemNames{$row->[0]} = $row->[1];
    # print "<$row->[0]> <$row->[1]> \n";
  }
}

[download]

Replies are listed 'Best First'.
Re: Getting info from 2 tables
by ignatz (Vicar) on Jul 30, 2002 at 17:05 UTC
    Ehhhhhh???? What's the question? Some of us monks are old sonny and you have to talk R E A L  C L E A R about what you want us to do for you. 
    ()-()
 \"/
  `
[reply]
      There are two tables (notifAlarmlog and rhic_au_fy_01)in a Sybase Db. The fields are , seperated. The names above the data are the column titles. The program here counts (total number of alarms, those <2 secs., those =>2 and <10 secs., and those >10 secs). Now I have to use the info from the second table (rhic_au_fy_01) to filter out the alarms in notifLogTable that occur w/in 10 secs. of a Quench Event (ev-yquench or ev-bquench).
[reply]
        Is this what you are trying to do:

        Hi, I've got two database tables and I'm trying to remove records from the first table that match a value in the second table.

        If that's the case, it sounds like what you need is a SQL join. For instance:
        (untested SQL code) 

        SELECT foo_id
FROM foo, bar 
WHERE paco < (waco + 10)
AND paco > (waco - 10)

        [download]
        Here's the steps to take:
        1. using DBI, run a SELECT SQL statement returning a result set based upon a JOIN of the two tables.
        2. Going through each value in the record set, run a DELETE of the records that you want to remove.
        I bet that there are some SQL gods out there who could do it through a single SQL statement, but for me SQL delete statements that complex give me the jitters. 
        ()-()
 \"/
  `
[reply]
[d/l]
Re: Getting info from 2 tables
by fruiture (Curate) on Jul 30, 2002 at 17:45 UTC

    I don't know your strange Database Module, so I can't be too concrete.

    1. read all rhicTimeUS values from rhic_au_fy_01 where the event is a ev-yquench or ev-bquench: SELECT rhicTimeUS FROM rhic_au_fy_01 where event ...
    2. store these times in a hash as the keys and set the value too.. undef. But chop off the decimals before (int()).
    3. iterate over the events in notifLogTable and for each UTimeStart again iterate from 5 seconds before to 5 seconds after and check each second if such a key exists() in the hash. If it does, stop that inner loop after deleting the current Event from the Table.

    HTH

    --
    http://fruiture.de
[reply]
Re: Getting info from 2 tables
by Cine (Friar) on Jul 30, 2002 at 20:15 UTC 
    SELECT SiteWideName,
sum(IF(notifAlarmLog.UTimeStart - notifAlarmLog.UTimeStop) < 2,1,0)) a
+s less2,
sum(IF(notifAlarmLog.UTimeStart - notifAlarmLog.UTimeStop) > 2 && noti
+fAlarmLog.UTimeStart - notifAlarmLog.UTimeStop) < 10,1,0)) as more2le
+ss10,
sum(IF(notifAlarmLog.UTimeStart - notifAlarmLog.UTimeStop) > 10,1,0)) 
+as more10
 FROM notifAlarmLog, fillEventsT WHERE
notifAlarmLog.UTimeStart < fillEventsT.rhicTimeUS-10 AND
notifAlarmLog.UTimeStart > fillEventsT.rhicTimeUS-10 AND
(fillEventsT.event = 'ev-bquench' OR fillEventsT.event = 'ev-yquench')
GROUP BY SiteWideName

    [download]


    T I M T O W T D I
[reply]
[d/l]

Back to Seekers of Perl Wisdom