Re: Tell Me If I've Got It Right

[George Jansen <GJANSEN@AFLCIO.ORG>]

You have my sympathy.

1. The join in the first subquery is meaningless. No aliases E or S are
defined in the subquery, so the condition checked is the same as in the
main query.
2. On the other hand, it's also pointless. I don't see a thing you gain
by including the check, since you're already checking implicitly in the
NOT IN.
3.  The second and third subqueries are either meaningless or
improperly written. If there's anything at all with a non-null
changedate in eqstatus, you will always get a row returned.

My best guess for what you really want would be something like



begin-select
count(e.eqnum) &eqcount
from equipment e, eqstatus s
where [$where] and e.eqnum = s.eqnum(+)
 and not exists
   (select 1
    from eqstatus s
    where [$where] and [$regulated] and s.eqnum = e.eqnum
      and changedate is not null)
end-select


>>> WolvertonJ@USA.REDCROSS.ORG 04/10/01 10:10AM >>>
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).