[Babase] Database warnings (and some errors that can't be caught dynamically)

Karl O. Pinc kop at meme.com
Thu Jul 20 11:50:00 EDT 2006


Hi,

I decided to attach the SQL rather than append it.

It would not hurt to run these queries against the
data and see what sort of warnings or errors show
their heads.

Catherine, I did send another mail to the list
straight from papio with the SQL as its content.
I think it got hung up waiting for list approval
because I didn't send it from my regular account
so you might want to approve or reject it.


Karl <kop at meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein
-------------- next part --------------
-- Copyright (C) 2004, 2005, 2006, Karl O. Pinc
--
--    Babase is free software; you can redistribute it and/or modify
--    it under the terms of the GNU General Public License as published by
--    the Free Software Foundation; either version 2 of the License, or
--    (at your option) any later version.
--
--    This program is distributed in the hope that it will be useful,
--    but WITHOUT ANY WARRANTY; without even the implied warranty of
--    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--    GNU General Public License for more details.
--
-- Karl O. Pinc <kop at meme.com>

-- DO NOT EDIT THIS FILE.  It was automatically generated.  Edit
-- the *.m4 files instead.  (Files _should_ be re-created by
-- typing 'make' at the command line.)



-- Run a bunch of queries reporting problems in the database.
--
-- $Id: check_integrity.m4,v 1.9 2004/11/08 02:23:04 kop Exp $

--
-- MATUREDATES
--

-- Warning condition
-- It's unusual to have a mature date within 3 years of the birthdate,
-- although 2 lodge females have managed it.
SELECT biograph.sname, birth, matured
       , 'Warning: Matdate within 3 years of birthdate.'
  FROM biograph, maturedates
  WHERE biograph.sname = maturedates.sname
        AND birth >= matured - CAST ('3 years' AS interval)
  ORDER BY biograph.sname;

-- Warning condition
-- It's unusual to have a mature date more than 7 years after the birthdate,
-- although ALE the immigrent anubis male has managed it.
SELECT biograph.sname, birth, matured
       , 'Warning: Matdate 7 years or more after birthdate.'
  FROM biograph, maturedates
  WHERE biograph.sname = maturedates.sname
        AND birth <= matured - CAST ('7 years' AS interval)
  ORDER BY biograph.sname;

-- Warning condition
-- It's unusual to have a mature female with no sexual cycles on
-- or after the sexual maturity date.
SELECT sname, matured
       , 'Warning: Mature female with no CYCPOINTS rows on or after maturity'
  FROM maturedates, biograph
  WHERE biograph.sname = maturedates.sname
        AND biograph.sex = 'F'
        AND maturdates.sname 
            NOT IN (SELECT cycpoints.sname
                           FROM cycpoints
                           WHERE cycpoints.sname = maturedates.sname
                                 AND cycpoints.date >= maturedates.date);

--
-- DISPERSEDATES
--

-- Warning condition
-- Dispersal before maturity.
SELECT dispersedates.sname, dispersedates.dispersed, maturedates.matured
       , 'Warning: Dispersed before maturity'
  FROM dispersedates LEFT OUTER JOIN maturedates
       ON (maturedates.sname = dispersedates.sname)
  WHERE maturedates.matured IS NULL
        OR dispersedates.dispersed < maturedates.matured
  ORDER BY dispersedates.sname;
--
-- PREGS
--

-- Not needed because we now generate the parity
-- -- Error condition
-- -- Parity, modulo 100, must be contigious.
-- SELECT pid, parity
--        , 'Error: Parity out of order'
--   FROM pregs outerpregs
--      , cycpoints as outercycpoints
--      , cycles as outercycles
--   WHERE parity % 100 != 1
--         AND outerpregs.conceive = outercycpoints.cpid
--         AND outercycles.cid = outercycpoints.cid
--         AND NOT EXISTS
--           (SELECT 1
--              FROM pregs AS innerpregs
--                 , cycpoints as innercycpoints
--                 , cycles AS innercycles
--              WHERE innercycles.sname = outercycles.sname
--                    AND innercycpoints.cid = innercycles.cid
--                    AND innerpregs.conceive = innercycpoints.cpid
--                    AND outerpregs.parity % 100 = innerpregs.parity % 100 + 1
--              LIMIT 1)
--   ORDER BY pid, parity;

-- Not needed, checked for in the triggers.
-- -- Warning condition
-- -- resumption of menses cycle should not have a mdate.
-- SELECT devent.sname, pregs.parity, pregs.pid
--        , mevent.date, mevent.edate, mevent.ldate
--        , 'Warning: Mdate on first cycle after pregnancy'
--   FROM pregs, cycpoints devent, cycpoints mevent
--   WHERE pregs.resume = devent.cpid
--         AND devent.cid = mevent.cid
--         AND mevent.code = 'M'
--   ORDER BY devent.sname, pregs.parity;

-- We check below that you can't cycle while pregnant.  
-- -- Warning condition
-- -- More than one pregs/female with no resume.
-- SELECT conception.sname, bad_pregs.pid, bad_pregs.parity
--       , 'Warning: more than one PREGS row/female with no resume cycle'
--   FROM pregs AS bad_pregs, pregs AS other_pregs
--        , cycpoints AS conception, cycpoints AS bad_conception
--   WHERE other_pregs.conceive = conception.cpid
--         AND bad_pregs.conceive = bad_conception.cpid
--         AND bad_conception.sname = conception.sname
--         AND bad_pregs.resume IS NULL
--         AND other_pregs.resume IS NULL
--         AND bad_pregs.oid != other_pregs.oid
--   ORDER BY conception.sname, bad_pregs.pid, bad_pregs.parity;
-- 
-- -- Warning condition
-- -- pregs with no resume must have largest Tdate for that female.
-- SELECT conception.sname, bad_pregs.pid, bad_pregs.parity, bad_pregs.date
--       , 'Warning: PREGS row with no resume cycle that is not the last pregnancy (by Ddate)'
--   FROM pregs AS bad_pregs, pregs AS other_pregs
--        , cycpoints AS conception, cycpoints AS bad_conception
--   WHERE other_pregs.conceive = conception.cpid
--         AND bad_pregs.conceive = bad_conception.cpid
--         AND bad_conception.sname = conception.sname
--         AND bad_pregs.resume IS NULL
--         AND bad_conception.date <= conception.date
--         AND bad_pregs.oid != other_pregs.oid
--   ORDER BY conception.sname, bad_pregs.pid, bad_pregs.parity;
-- 
-- -- pregs with no resume must have largest parity for that female.
-- SELECT conception.sname, bad_pregs.pid, bad_pregs.parity, bad_pregs.date
--       , 'Warning: PREGS row with no resume cycle that is not the last pregnancy (by parity)'
--   FROM pregs AS bad_pregs, pregs AS other_pregs
--        , cycpoints AS conception, cycpoints AS bad_conception
--   WHERE other_pregs.conceive = conception.cpid
--         AND bad_pregs.conceive = bad_conception.cpid
--         AND bad_conception.sname = conception.sname
--         AND bad_pregs.resume IS NULL
--         AND bad_pregs.parity <= other_pregs.parity
--         AND bad_pregs.oid != other_pregs.oid
--   ORDER BY conception.sname, bad_pregs.pid, bad_pregs.parity;

-- Warning condition
-- Pregs with no T date in the conception cycle will not
-- have the conception cycle referenced in the repstats rows
-- for the cycling period preceeding the pregnancy.
-- (This query might benefit from use of the cycgapdays table.)
--       , 'Warning: PREGS conception cycle has no T date (but there is no gap) so REPSTATS rows recording the cycling state before the pregnancy do not reference the conception cycle'
SELECT dcycle.sname, pregs.pid, pregs.conceive
       , 'Warning: PREGS conception cycle has no T date, and is not preceeded by a gap'
  FROM pregs, cycpoints AS devent, cycles AS dcycle
  WHERE pregs.conceive = devent.cpid
        AND devent.source != 'E'
        AND dcycle.cid = devent.cid
        AND NOT EXISTS 
          (SELECT 1 FROM cycpoints AS tevent
                  WHERE tevent.cid = devent.cid
                        AND tevent.code = 'T')
        AND NOT EXISTS
          (SELECT 1 FROM cycgaps
                    WHERE cycgaps.sname = dcycle.sname
                          AND cycgaps.code = 'E'
                          AND cycgaps.date >= (SELECT prior.date
                                         FROM cycpoints AS prior
                                              , cycles AS pcycles
                                         WHERE pcycles.sname = dcycle.sname
                                               AND prior.cid = pcycles.cid
                                               AND prior.date < devent.date
                                         ORDER BY prior.date DESC
                                         LIMIT 1)
                          AND cycgaps.date < devent.date)
        AND NOT EXISTS
          (SELECT 1 FROM cycgaps
                    WHERE cycgaps.sname = dcycle.sname
                          AND cycgaps.code = 'P'
                          AND cycgaps.date = (SELECT prior.date
                                         FROM cycpoints AS prior
                                             , cycles AS pcycles
                                         WHERE pcycles.sname = dcycle.sname
                                               AND prior.cid = pcycles.cid
                                               AND prior.date < devent.date
                                         ORDER BY prior.date DESC
                                         LIMIT 1))
  ORDER BY dcycle.sname, pregs.pid;

-- Warning condition
-- Pregnancy exceeds 178 days.
-- (This query could probably benefit by using the cycgapdays table.)
SELECT pregs.pid
       , 'Warning: Pregnancy exceeds 178 days (and there are no gaps in observation)'
  FROM pregs, biograph AS mom, cycpoints, cycles
  WHERE pregs.resume IS NULL
        AND NOT EXISTS (SELECT 1
                          FROM biograph AS child
                          WHERE child.pid = pregs.pid)
        AND cycpoints.cpid = pregs.conceive
        AND cycles.cid = cycpoints.cid
        AND mom.sname = cycles.sname
        AND mom.statdate >= cycpoints.date + CAST('178 days' AS interval)
        AND NOT EXISTS (SELECT 1
                          FROM cycgaps
                          WHERE cycgaps.sname = cycles.sname
                                AND cycgaps.state = 'S'
                                AND cycgaps.date > cycpoints.date
                                AND cycgaps.date <= cycpoints.date
                                            + CAST('178 days' AS interval))
        AND NOT EXISTS (SELECT 1
                          FROM cycgaps
                          WHERE cycgaps.sname = cycles.sname
                                AND cycgaps.state = 'E'
                                AND cycgaps.date >= cycpoints.date
                                AND cycgaps.date < cycpoints.date
                                           + CAST('178 days' AS interval))
  ORDER BY pregs.pid;

--
-- Error condition
-- You can't cycle while pregnant
SELECT pregs.pid, later.date, later.code
       , 'Error: Female cycling while pregnant'
  FROM pregs, cycpoints AS c, cycpoints AS later
       , cycles AS ccycle, cycles AS lcycle
  WHERE pregs.resume IS NULL
        AND c.cpid = pregs.conceive
        AND ccycle.cid = c.cid
        AND lcycle.sname = ccycle.sname
        AND later.cid = ccycle.cid
        AND later.date > c.date
        AND later.code = 'T'
        AND NOT EXISTS
         (SELECT 1
            FROM cycgaps
            WHERE cycgaps.sname = ccycle.sname
                  AND cycgaps.code = 'E'
                  AND cycgaps.date >= c.date)
        AND NOT EXISTS
         (SELECT 1
            FROM cycgaps
            WHERE cycgaps.sname = ccycle.sname
                  AND cycgaps.code = 'P'
                  AND cycgaps.date = c.date)
  ORDER BY pregs.pid, later.date, later.code;


--
-- CYCPOINTS
--

--
-- Must have Mdate, Tdate, Ddate order, unless there's a gap.
--
-- Test prior cycpoint (on this side of any gaps.)
-- (The freaky NOT() constructs are to deal with the 3 valued
-- logic we get when NULLs are returned from the outer join.)
SELECT this_cycles.sname
     , prior_cycpoints.date AS "Earlier date"
     , prior_cycpoints.code AS "Earlier code"
     , this_cycpoints.date AS "Later date"
     , this_cycpoints.code AS "Later code"
     , 'Error: CYCPOINTS must be in Mdate, Tdate, Ddate order unless there is a gap'
  FROM cycpoints AS this_cycpoints
    JOIN cycles AS this_cycles
      ON (this_cycle.cid = this_cycpoints.cid)
    CROSS JOIN (SELECT sub.date, sub.code
                  FROM cycles AS prior_cycles
                    LEFT OUTER JOIN cycgaps
                      ON (cycgaps.sname = this_cycle.sname
                          AND cycgaps.code = 'E'
                          AND cycgaps.date < this_cycpoints.date)
                    JOIN cycpoints AS sub
                      ON (sub.cid = prior_cycles.cid
                          AND sub.code != 'B'
                          AND ((this_cycpoints.code = 'T'
                                -- M and Tdates can be on the same date
                                AND sub.date <= this_cycpoints.date
                                AND sub.cpid != this_cycpoints.cpid)
                               OR (this_cycpoints.code != 'T'
                                   AND sub.date < this_cycpoints.date))
                          AND NOT(sub.date <= cycgaps.date))
                  WHERE prior_cycles.sname = this_cycle.sname
                  ORDER BY sub.date DESC
                  LIMIT 1) AS prior_cycpoints
  WHERE this_cycpoints.code != 'B'
        AND ((this_cycpoints = 'M'
              AND prior_cycpoints != 'D')
             OR (this_cycpoints = 'T'
                 AND prior_cycpoints != 'M')
             OR (this_cycpoints = 'D'
                 AND prior_cycpoints != 'T'))
  ORDER BY this_cycles.sname, this_cycpoints.date;

-- Warning condition
-- No cycpoints on a mature female
SELECT maturedates.sname, maturedates.matured
       , 'Warning: Mature female has no Mdate, Tdate, or Ddate CYCPOINTS rows'
  FROM maturedates, biograph
  WHERE biograph.sex = 'F'
        AND maturedates.sname = biograph.sname
        AND NOT EXISTS (SELECT 1
                    FROM cycpoints, cycles
                    WHERE cycles.sname = biograph.sname
                          AND cycpoints.cid = cycles.cid
                          AND cycpoints.code != 'B'
                    LIMIT 1)
  ORDER BY maturedates.sname;

-- Error condition
-- When Tdates are the first sexual cycle event that occurs after
-- (but not on) the beginning of observation, then the cycgaps
-- state at the beginning of observation must be M, P, or L.
SELECT cycgaps.sname, cycgaps.date, cycgaps.state
     , cycpoints.code, cycpoints.date
     , 'Error: The CYCGAPS.State does not correspond with having a Tdate as the first CYCPOINTS in the series'
  FROM cycgaps, cycles, cycpoints
  WHERE cycgaps.code = 'S'
        AND cycgaps.state != 'M'
        AND cycgaps.state != 'P'
        AND cycgaps.state != 'L'
        AND cycles.sname = cycgaps.sname
        AND cycpoints.cid = cycles.cid
        AND cycpoints.code = 'bb_turgesent'
        AND cycpoints.date > cycgaps.date
        AND cycpoints.date = (SELECT cp.date
                                FROM cycles AS cy, cycpoints AS cp
                                WHERE cy.sname = cycgaps.sname
                                      AND cy.series = cycles.series
                                      AND cp.cid = cy.cid
                                      AND cp.date >= cycgaps.date
                                ORDER BY cp.date
                                LIMIT 1)
  ORDER BY cycgaps.sname, cycgaps.date;

-- Error condition
-- An estimated Ddate that's not a conception event.
SELECT cycles.sname, cycpoints.date, cycpoints.code, cycpoints.source
       'Error: An estimated Ddate that is not a conception event'
  FROM cycles, cycpoints
  WHERE cycpoints.code = 'D'
        AND cycpoints.source = 'E'
        AND cycpoints.cpid NOT IN (SELECT conceive FROM pregs)
        AND cycles.cid = cycpoints.cid
  ORDER BY cycles.sname, cycpoints.date;

-- 
-- CYCLES
--

-- -- Warning condition
-- -- Female's first sexual cycle should not have a mdate.
-- SELECT sname, cid, mdate, ddate, 'Warning: mdate on first sexual cycle'
--   FROM cycles
--   WHERE seq = 1
--         AND mdate IS NOT NULL
--   ORDER BY sname;

-- -- The update and delete triggers do not check for sequentiality of
-- -- the sequence number.
-- SELECT cid, seq, sname, 'CYCLES.Seq out of order'
--   FROM cycles AS outercycles
--   WHERE seq != 1
--         AND NOT EXISTS (SELECT innercycles.seq
--                           FROM cycles AS innercycles
--                           WHERE outercycles.sname = innercycles.sname
--                                 AND outercycles.seq = innercycles.seq + 1)
--   ORDER BY sname, seq;

-- Error condition
-- There should be no cycles without cycpoints.
SELECT cid, sname, seq, series
     , 'Error: CYCLES row has no CYCPOINTS'
  FROM CYCLES
  WHERE NOT EXISTS (SELECT 1 FROM cycpoints WHERE cycpoints.cid = cycles.cid)
  ORDER BY sname, seq;

-- Error condition
-- The combination of sname and seq should be unique.
SELECT ca.sname, ca.seq, ca.cid AS "one cid", cb.cid AS "another cid"
     , 'Error: the combination of Sname and Seq on CYCLES should be unique'
  FROM cycles AS ca, cycles AS cb
  WHERE ca.sname = cb.sname
        AND ca.seq = cb.seq
        AND ca.cid != cb.cid
  ORDER BY ca.sname, ca.seq;
    
-- Error condition
-- There should be no NULL seqs.
SELECT cid, sname
     , 'Error: CYCLES should not have NULL Seq values'
  FROM cycles
  WHERE seq IS NULL
  ORDER BY sname, cid;
    
-- Error condition
-- There should be no NULL series.
SELECT cid, sname
     , 'Error: CYCLES should not have NULL Series values'
  FROM cycles
  WHERE series IS NULL
  ORDER BY sname, cid;

--
-- SEXSKINS (and cycles)
--

-- Warning condition
-- D dates are not normally after the first measurement after
-- the largest measurement of the cycle.
-- SELECT cycles.sname, cycles.cid, cycles.ddate
--        , outersexskins.date, outersexskins.size
--        , 'Warning: D date after the first measurement following the largest sexskin measurement of the cycle'
--     FROM sexskins AS outersexskins, cycles
--     WHERE outersexskins.cid = cycles.cid
--           AND date = (SELECT MIN(middlesexskins.date)
--                         FROM sexskins AS middlesexskins
--                         WHERE middlesexskins.cid = cycles.cid
--                               AND middlesexskins.date >
--                                  (SELECT MAX(innersexskins.date)
--                                     FROM sexskins as innersexskins
--                                     WHERE innersexskins.cid = cycles.cid
--                                           AND innersexskins.size =
--                                               (SELECT MAX(maxsize.size)
--                                                  FROM sexskins AS maxsize
--                                                  WHERE maxsize.cid
--                                                        = cycles.cid)))
--           AND (outersexskins.date < cycles.ddate
--                OR outersexskins.date < cycles.eddate)
--     ORDER BY cycles.sname, cycles.cid;
SELECT cycles.sname, cycles.cid, cycles.ddate
       , outersexskins.date, outersexskins.size
       , 'Warning: D date after the first measurement following the largest sexskin measurement of the cycle'
    FROM sexskins AS outersexskins, cycles
    WHERE outersexskins.cid = cycles.cid
          AND date = (SELECT middlesexskins.date
                        FROM sexskins AS middlesexskins
                        WHERE middlesexskins.cid = cycles.cid
                              AND middlesexskins.date >
                                 (SELECT innersexskins.date
                                    FROM sexskins as innersexskins
                                    WHERE innersexskins.cid = cycles.cid
                                          AND innersexskins.size =
                                              (SELECT maxsize.size
                                                 FROM sexskins AS maxsize
                                                 WHERE maxsize.cid
                                                       = cycles.cid
                                                 ORDER BY maxsize.size DESC
                                                 LIMIT 1)
                                    ORDER BY innersexskins.date DESC
                                    LIMIT 1)
                        ORDER BY middlesexskins.date
                        LIMIT 1)
          AND (outersexskins.date < cycles.ddate
               OR outersexskins.date < cycles.eddate)
    ORDER BY cycles.sname, cycles.cid;

--
-- CENSUS
--

-- Warning condition
-- The first censused group is not the individual's matgrp.
-- SELECT biograph.sname, biograph.birth, biograph.matgrp
--        , date AS "census date", grp AS censusgrp
--        , 'Warning: First non-absent census is not in Matgrp'
--     FROM biograph, census
--     WHERE biograph.sname = census.sname
--           AND census.status != 'A'
--           AND date = (SELECT MIN(inn.date)
--                                     FROM census AS inn
--                                     WHERE inn.sname = biograph.sname
--                                           AND inn.status != 'A')
--           AND grp != biograph.matgrp
--     ORDER BY biograph.sname;
SELECT biograph.sname, biograph.birth, biograph.matgrp
       , date AS "census date", grp AS censusgrp
       , 'Warning: First non-absent census is not in (non 9.0) Matgrp'
    FROM biograph, census
    WHERE biograph.sname = census.sname
          AND biograph.matgrp != '9.0'
          AND census.status != 'A'
          AND date = (SELECT inn.date
                                    FROM census AS inn
                                    WHERE inn.sname = biograph.sname
                                          AND inn.status != 'A'
                                    0RDER BY inn.date
                                    LIMIT 1)
          AND grp != biograph.matgrp
    ORDER BY biograph.sname;


-- Error condition
-- ''D'' census rows should have a demography note, unless the note
-- is in the process of being entered.
-- Note that this _could_ be accomplished with a DEFERRED CONSTRAINT
-- in the table definition.  I'm not doing it that way partly
-- because this is already done and partly to give people
-- flexibility on the front end so they can enter demography notes without
-- transactions in their front-end.
SELECT census.cenid, census.sname, census.date, census.status
       , 'Error: CENSUS rows with a Status of D must have an associated DEMOG row'
    FROM census
    WHERE status = 'D'
          AND cenid NOT IN (SELECT cenid FROM demog)
    ORDER BY census.sname, census.date, census.cenid;

-- Warning condition
-- No census row
SELECT biograph.sname, 'Warning: Individuals with a Sname but no (non absent) CENSUS row'
    FROM biograph
    WHERE sname IS NOT NULL
          AND sname NOT IN (SELECT census.sname
                                   FROM census
                                   WHERE census.sname = biograph.sname
                                         AND census.status != 'A')
    ORDER BY sname;

--
-- INTERACT & PARTS
--

-- Error condition
-- Each Interact row should have exactly two corresponding Parts rows.
SELECT iid, act, date, 'Error: INTERACT rows must have exactly 2 corresponding rows on PARTS'
  FROM interact
  WHERE (SELECT count(*)
           FROM parts
           WHERE interact.iid = parts.iid) != 2
  ORDER BY date, act, iid;

-- Warning condition
-- Interactions should be between individuals in the same supergroup.
SELECT interact.iid, interact.act, interact.date
       , actor.sname, actorgrp.supergroup
       , actee.sname, acteegrp.supergroup
       , 'Warning: Interactions should be between individuals in the same supergroup'
  FROM interact, parts AS actor, parts AS actee
       , members AS acteembr, members AS actormbr
       , groups AS acteegrp, groups AS actorgrp
  WHERE actor.iid = interact.iid
        AND actor.role = 'R'
        AND actormbr.date = interact.date
        AND actormbr.sname = actor.sname
        AND actorgrp.gid = actormbr.grp
        AND actee.iid = interact.iid
        AND actee.role = 'E'
        AND acteembr.date = interact.date
        AND acteembr.sname = actee.sname
        AND acteegrp.gid = acteembr.grp
        AND actorgrp.supergroup != acteegrp.supergroup
  ORDER BY interact.date, interact.act, interact.iid, actor.sname, actee.sname;
  

--
-- Samples and Points
--

-- Error condition
-- Samples.miniss should equal the number of related points data rows.
SELECT samples.sid, samples.date, samples.grp, samples.sname
       , samples.miniss, COUNT(points.pid)
       , 'Error: SAMPLES.Miniss must equal the total number of releated POINTS data rows'
  FROM samples, points
  WHERE samples.sid = points.sid
  GROUP BY samples.sid
  HAVING samples.miniss != count(points.pid)
  ORDER BY samples.sid;

-- Error condition
-- The female point samples must all have fpoints.
SELECT samples.sid, samples.date, samples.grp, samples.stype, samples.sname
       , points.pid, points.time
       , 'Error: Female point samples must have related FPOINTS rows'
  FROM samples, points
  WHERE samples.stype = 'F'
        AND points.sid = samples.sid
        AND points.pntid NOT IN (SELECT pntid FROM fpoints)
  ORDER BY samples.sid, points.pntid;

-- Warning condition
-- The juvenile point samples should be taken on individuals
-- less than 4 years old.
SELECT samples.sid, samples.date, samples.grp, samples.stype, samples.sname
       , biograph.birth
       , 'Warning: Juvenile point samples should be for individuals < 4 years of age'
  FROM samples, biograph
  WHERE samples.sname = biograph.sname
        AND samples.stype = 'J'
        AND samples.date - CAST ('4 years' AS Interval) >= biograph.birth;

-- Warning condition
-- The juvenile point samples should not be taken on individuals
-- more than 90 days past their maturity date.
SELECT samples.sid, samples.date, samples.grp, samples.stype, samples.sname
       , maturedates.matured
       , 'Warning: Juvenile point sample taken more than 90 days past maturity date'
  FROM samples, maturedates
  WHERE samples.sname = maturedates.sname
        AND samples.stype = 'J'
        AND samples.date - CAST ('90 days' AS Interval) > maturedates.matured;


---
--- CYCSTATS
---

-- Warning condition
-- REPSTATS say an individul is cycling but CYCSTATS has no rows.
SELECT sname, date
       , 'Warning: REPSTATS indicates cycling but there is not a CYCSTATS row'
  FROM repstats
  WHERE NOT EXISTS (SELECT 1
                      FROM cycstats
                      WHERE cycstats.sname = repstats.sname
                            AND cycstats.date = repstats.date)
        AND repstats.state = 'C'
  ORDER BY sname, date;



More information about the Babase mailing list