Re: Searching (sybase) Database and SQL help (was: Neophyte needs help.)

[BlueBlazerRegular]

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";
}

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

[mpeppler]

Neat.

Unfortunately that SQL statement won't work with Sybase - specifically the

select ... 
  from (select ...) alias

syntax is not supported.

Usually the same thing can be achieved with temporary tables (which is what the above will do under the covers anyway), or with some interesting GROUP BY and HAVING clauses.

Michael

[BlueBlazerRegular]

Whoops. I'm currently using SQL Server which does allow these types of things and so I thought|assumed that Sybase did too.

A lesson to all you young Monks out there - this is what happens when you don't test your code (or look in the book to make sure it'll work).

Going from Sybase to Oracle and now to SQL Server has left me confused on who allows what. I guess I could stay with just the 'core' bits, but that takes all the fun out of things.

Pat

[mpeppler]

Yeah - MS-SQL (and Oracle) have added a lot of syntactic sugar at the SQL level...

Michael