Re: trigger advice needed

[Pete Hall <peteh@xxxxxxxxxx>]

At 07:00 10/26/1998 , David Prowak wrote:
>           parking tickets.  When a tickets paid, it's status changes from
>a 'N' to a '0'.
>           This individual would then have 4 unpaid tickets, so they would
>not be 
>           eligible to receive a Default Judgment.  I then need to change
>the status 
>           of the other 4 tickets from 'N' to whatever it was before the
>Default Judgment 
>           Notice was sent.
>

Just some food for thought. The way I've handled this kind of thing is to use a 
summary view something like:

  CREATE VIEW MYLIB/SCOFFLAW
    (SCOFFID,
     NBRTICKETS)
  AS SELECT 
     SCOFFID,
     COUNT(*)
  FROM TICKETS
  GROUP BY
     SCOFFID

And follow that up with a selection of all of the tickets for the scofflaws. 
You'd probably want to explicitly list the fields here, and maybe you'd want to 
use a join so that the summary file was primary, but something like:

  SELECT * FROM TICKETS WHERE
     SCOFFID IN
       (SELECT SCOFFID FROM SCOFFLAW
          WHERE NBRTICKETS > 4)
  ORDER BY SCOFFID,
           TICKETNBR

You need to play around a little creating some indexes so the system can 
retrieve the data as efficiently as possible. Really just by running the query 
in debug, looking at the messages in the joblog and doing what it says. Once 
that's done, it's all automagic. Obviously, SQL is the hot setup, but even if 
you don't have SQL, you can create summary views and select records into an 
outfile with QMQuery. No more status code updates. This stuff even runs pretty 
lickety split over a few 100k records. I'm not sure what would happen if you 
had a really large file - probably would be less impressive.

     

Pete Hall
peteh@inwave.com 
http://www.inwave.com/~peteh/

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---