Please, use <code> </code> tags when posting your code.

As to the question, a quick fix solution you could try is getting the data from the database sorted descending by time, so that the newest times come first. Then, you can push the first occurence of a job name onto a list of data rows, and when the while loop ends, you can print the rows.

# I'm not sure if it's the correct statement
# for getting the data sorted newest first, test it first
&DBEXEC("select proc_eventvu.job_name, jobst.status, proc_eventvu.stam
+p, proc_eventvu.text
        from proc_eventvu, jobst
        where proc_eventvu.joid = jobst.joid
            and proc_eventvu.event in (112,$eventnumber)
            and jobst.status = $statusnumber
        order by proc_eventvu.stamp desc");

my %already_processed;
my @results_lol;

while(@status = &dbnextrow($dbproc)) {
    $name = ($status[0]);
    $jstatus = &JOBSTATUS($status1);
    $estamp = ($status2);
    $etext = ($status3);
    
    # only the job data for a jobname
    # that wasn't processed before 
    # make it to the result list
    unless (defined $already_processed{$name}) {
        push @results_lol, [($name, $jstatus, $estamp, $etext)];
        $already_processed{$name} = 1;
    }
}

# here, if you want the rows sorted by name, you could 
# write some necessary code

for my $row_ref (@results_lol) {
    my ($name, $jstatus, $estamp, $etext) = @$row_ref;
    write;
}
[download]

It's untested, but try if it works. And I wholeheartedly second the suggestion about  use strict; use warnings;, as well as the advice about the JOBSTATUS subroutine.

Regards,
Luke

use strict;
use warnings;
use DBI;

my @alljobData = ();
my $dbh = DBI->connect(...);
my $stmt = "SELECT JOBNAME, STATUS, TIMESTAMP, COMMENT FROM YOUR_TABLE
+S WHERE JOBNAME = ? AND STATUS = ? ORDER BY TIMESTAMP DESC";
my $sth = $dbh->prepare($stmt);
foreach my $job (@jobname){
  my $rc = $sth->execute($job, "HOLD");
  #either check for an error or let failing on fetch do that for you
  if(my @thisjobData = $sth->fetchrow_array){
    push(@allJobData, @thisjobData);
  }
}
[download]

There are several ways to do it, depending on your database. The most portable is:

SELECT t.jobname
  , t.status
  , t.timestamp
  , t.comment
FROM your_table t
  JOIN (
      SELECT jobname, MAX(timestamp) as timestamp
      FROM your_table
      GROUP BY jobname
    ) max_timestamp
    ON max_timestamp.jobname = t.jobname
      AND max_timestamp.timestamp = t.timestamp
[download]

Of course this assumes that you don't have 2 entries with the same name and the same timestamp. It is usually less efficient than selecting back and filtering outside of the database.

Google tells me that Sybase doesn't yet support the better SQL 2003 solution of analytic queries.

Also a note about your solution. I would strongly recommend ordering on jobname and then timestamp. This will put all of the records for each jobname together, making it easy to figure out which is first and which you should throw. Otherwise you need to keep a potentially large hash of which jobs you've seen. (You skipped that step in your code.)

Heh, I just rolled my own first() and last() aggregators in Postgresql yesterday because I wanted something like this (googling for analytic queries found a somewhat related link that mentioned first() and last() aggregators as I was curious if my solution was in-line with SQL 2003's solution).

The process was a bit more interesting and difficult than I expected. It convinced me that Postgresql doesn't have a general solution for user-defined polymorphic aggregators because it doesn't allow polymorphic user-defined data types (you can't use "anyelement" as part of a "create type" construct -- at least in the versions I was dealing with, 8.2 and 8.3).

So I was able to create some polymorphic aggregators but can see cases where these approaches would not work (and the first route I tried failed).

Read more... (1479 Bytes)

The following test query:

select first(v), first_nonnull(v), last(v), last_nonnull(v)
from ( select NULL::text as v union all
    select 'one' as v union all select 'two' as v ) as q
union all
select first(v), first_nonnull(v), last(v), last_nonnull(v)
from ( select 'one'::text as v union all
    select 'two' as v union all select NULL as v ) as q;
[download]

produces the following results:

 first | first_nonnull | last | last_nonnull
-------+---------------+------+--------------
       | one           | two  | two
 one   | one           |      | two
(2 rows)
[download]

- tye        

In Sybase you can use a a group by and a having clause, even without using any aggregates in the main query.

So something like

select jobname, status, timestamp, comment
from the_table
group by jobname
having timestamp = max(timestamp)
[download]

should return something reasonable without the need for a sub-query.

Michael

The SQL for looking up a unique column will roughly look like this(depending upon your database),

SELECT DISTINCT(JOBNAME), STATUS, TIMESTAMP, COMMENT FROM YOUR_TABLES 
+WHERE STATUS = ? ORDER BY TIMESTAMP DESC
[download]

Now you remove that loop for looking up individual job names.

With DB2, using DISTINCT in that manner won't guarantee that you get the most recent result. DISTINCT will be evaluated before the ORDER BY clause and may not find the most recent record first. I'm not sure whether this behavior is standard among databases or not.

From the db2 doc:

When values match in the specified columns of two or more rows, the distinct operator retains only one of the duplicate rows in the result data set and discards the others. When duplicate rows are found, only the first row that is read during processing is retained. Because the order of rows is not guaranteed during processing, choose carefully when you select columns for the distinct operation.

To ensure you get the most recent occurrence, you'd probably need to use a temp table in the statement (at least that's how I've seen it done).

sub JOBSTATUS {
    my %foo = (
     1 => "RUNNING",
     3 => "STARTING",
     4 => "SUCCESS",
     5 => "FAILURE",
     6 => "TERMINATED",
     7 => "ON_ICE",
     8 => "INACTIVE",
     9 => "ACTIVATED",
     10 => "RESTART",
     11 => "ON_HOLD",
     12 => "QUE_WAIT", 
    );

   return $foo{ $_[0] };
}
[download]

• Why it's stupid to `use a variable as a variable name` - part 1
• Why it's stupid to `use a variable as a variable name` - part 2
• Why it's stupid to `use a variable as a variable name` - part 3