To get this right, I'd normally need some more information on how the database tables are laid out. But for now, I'll just guess at the table layout and work from there. This solution assumes the following:

1) The table has all the past alarms ('warning' and 'ok now' messages). For example:

      Topic      Category    DateTime
      Process1   WARNING     06/25/2002 12:32:14.001
      Process2   WARNING     06/25/2002 13:45:12.020
      Process1   OK NOW      06/25/2002 13:56:12.050
      Process3   WARNING     06/25/2002 14:10:01.123
      Process2   OK NOW      06/25/2002 14:15:10.005
      Process1   WARNING     06/25/2002 14:20:15.001
      Process3   OK NOW      06/25/2002 14:32:10.005
      Process2   WARNING     06/25/2002 14:46:45.001

2) You only want to see the latest alarm(s) for each process.

If the above assumptions are correct, then the following should get you close. The bulk of the effort is in the SQL statement... 

#! /usr/local/bin/perl-w 
# A perl program to do read the notifAlarmTable

use FileHandle;
use IPC::Open2;
use Symbol;
# use Tk;
use Sybase::CTlib;
use Time::Local;
use strict;
my $dbAlarm=new Sybase::CTlib 'harmless','harmless','OPSYB1','';
$dbAlarm->ct_sql("use TomTest");

#
# WARNING! UNTESTED CODE FOLLOWS!!!
#

#
# Build the SQL statement.  This statement will pull together
#    a list of all Topics, with their most current alarm start
#    ('WARNING') and alarm stop ('OK NOW').
#
# The parsing of the SQL statement is left as an exercise for the
#    reader...
#
my $sql = "select q1.Topic,
                  q2.DateTime,
                  q3.DateTime
           from ( select Topic
                  from notifAlarmEntry
                  group by Topic ) q1
                ( select Topic,
                         Category,
                         max(DateTime)
                  from notifAlarmEntry
                  where Category = 'WARNING'
                  group by Topic, Category ) q2
                ( select Topic,
                         Category,
                         max(DateTime)
                  from notifAlarmEntry
                  where Category = 'OK NOW'
                  group by Topic, Category ) q3
           where q1.Topic *= q2.Topic
             and q1.Topic *= q3.Topic
           order by nAE.Topic";
       

#
# get an array containing all the records returned from
#    the SQL statement
#
my( @records ) = $dbAlarm->ct_sql( $sql );

#
# look thru each retrieved record
#
foreach my $record ( @records )
{
   #
   # Put all the fields from the array entry into their own fields.
   #
   # Not really needed, but it's easier to understand what you
   #    are doing if things have meaningful names.
   #
   # Note: I'm handling each field individually, but if you know
   #       what the field separator is, then you can do the
   #       following:
   #
   #          my( $topic,
   #              $alarm_start,
   #              $alarm_stop ) = split( /X/o, $record );
   #
   #       (where X is the field separator).  Since I don't know
   #       Sybase::CTlib all that well (ok, at all), I just did the
   #       simple thing.
   #
   my $topic       = $record->[0];
   my $alarm_start = $record->[1];
   my $alarm_stop  = $record->[2];


   # if the 'ok now' date is from before the 'warning' date, 
   #   reset the 'ok now' date (since it isn't the message
   #   associated with this warning)
   if ( $alarm_stop < $alarm_start )
   {
      $alarm_stop = "";
   }
      
   print "$topic  $alarm_start  $alarm_stop\n";
}

[download]

This should get you the following results (using the sample data from above):

      Process1  06/25/2002 14:20:15.001
      Process2  06/25/2002 14:46:45.001
      Process3  06/25/2002 14:10:01.123  06/25/2002 14:32:10.005

Of course, if the data is in a different layout, then this solution won't necessarily work.

And while we're here - a few comments about your code. First, congratulations on using '-w' and 'use strict' - they've saved my butt many times. But try not to use 'select *' in your SQL statements. Only pull the fields you need - you save memory that way and by explicitly requesting the fields you want you save yourself some heartbreak later on if someone adds columns to the table. And one final thing - indent your code. It's hard to tell where the 'foreach' statment ends when everything if pushed over to the left side.

Pat

In reply to Re: Searching (sybase) Database and SQL help (was: Neophyte needs help.) by BlueBlazerRegular
in thread Searching (sybase) Database and SQL help (was: Neophyte needs help.) by cjacksonjr

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.