Tell Me If I've Got It Right

["Wolverton, Jeff" <WolvertonJ@USA.REDCROSS.ORG>]

To all:

Please review this short count routine below (I didn't write it) and tell me
if my understanding of the routine is correct.  If my understanding of the
routine is incorrect, please open my understanding.

For the record, I have 15 years of programming experience, but am new to
SQR3.

Respectfully,

Jeff Wolverton
American Red Cross
Falls Church, Virginia


        BEGIN-PROCEDURE count_records
        begin-select
        COUNT(E.EQNUM)  &eqcount
                from equipment e, eqstatus s
        ! Mod8 !        where  [$where] and s.eqnum(+)=e.eqnum AND E.EQNUM
NOT IN (SELECT EQNUM FROM EQSTATUS WHERE
                where  [$where] and s.eqnum(+)=e.eqnum AND [$regulated2] AND
E.EQNUM NOT IN (SELECT EQNUM FROM EQSTATUS WHERE
                changedate in (SELECT MAX(s.CHANGEDATE)
                from eqstatus S, EQUIPMENT E WHERE e.EQNUM=s.EQNUM(+)GROUP
BY e.EQNUM)

        !       Mod4
                AND code = $disposed)
        ! Mod8  AND [$regulated2] and code = $disposed)
        !       Mod4

        end-select
        END-PROCEDURE



        Here's my dissect:

        COUNT(E.EQNUM)  &eqcount
                from equipment e, eqstatus s
        --> count the e.eqnum records and call the count field eqcount

        ! Mod8 !        where  [$where] and s.eqnum(+)=e.eqnum AND E.EQNUM
NOT IN (SELECT EQNUM FROM EQSTATUS WHERE
                where  [$where] and s.eqnum(+)=e.eqnum AND [$regulated2] AND
E.EQNUM NOT IN (SELECT EQNUM FROM EQSTATUS WHERE
                changedate in (SELECT MAX(s.CHANGEDATE)
                from eqstatus S, EQUIPMENT E WHERE e.EQNUM=s.EQNUM(+)GROUP
BY e.EQNUM)

        !       Mod4
                AND code = $disposed)
        ! Mod8  AND [$regulated2] and code = $disposed)
        !       Mod4
        --> First it would be nice to know the values in $where and
$regulated2.
        $where will be an equipment number, and $regulated 2 will contain
one of three values: 1) e.eq9=''Y', 2) e.eq9=''N', or 3) 1=1

        ! Mod8 !        where  [$where] and s.eqnum(+)=e.eqnum AND E.EQNUM
NOT IN
        --> the line has a comment, but it also has an end comment, so the
logic in this line says to count the record if the equipment number in
$where is found and equipment number from eqstatus = equipment number in
equipment (the plus sign says I want all the eqstatus records regardless)
and equipment number is not in the next query

        (SELECT EQNUM FROM EQSTATUS WHERE
                where  [$where] and s.eqnum(+)=e.eqnum AND [$regulated2] AND
E.EQNUM NOT IN
        --> the logic is the same as the last piece with the addition of
also including the value of $regulated2

        (SELECT EQNUM FROM EQSTATUS WHERE
                changedate in (SELECT MAX(s.CHANGEDATE)
        --> this logic selects equipment numbers from eqstatus if the field
changedate (in eqstatus) is = the maximum changedate, e.g., if the highest
changedate is 20010409, then that's the date with the maximum changedate

        from eqstatus S, EQUIPMENT E WHERE e.EQNUM=s.EQNUM(+) GROUP BY
e.EQNUM)
        --> the selection is from eqstatus, and equipment.  When retrieving
records from equipment the equipment number must match equipment number in
eqstatus (again we want the records in eqstatus regardless).  Group the
records by equipment number in equipment.

        AND code = $disposed)
        --> this is the last piece because the other lines are comments.
This line says if code = the value in $disposed.


        Let's simplify the "weeding out" logic.  Let's says the value of
$regulated2 = "e.eq9='Y'", and the maximum change date is April 9, 2001
(20010409).  This would mean that records in eqstatus with a changedate of
20010409 and/or records in equipment with eq9='Y' would be bypassed because
the logic said to exclude them (E.EQNUM NOT IN).