[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