Hi,

you might want to try something like this (that table must have
unique indentifier like statment_id (order number or alike) we will
call it ID)

select st1.SDATE, count(st2.OPTYPE) NOOBS, count(st3.OPTYPE) NOCBS
from
STATMENTS st1
left outer join STATMENTS st2 on (st1.ID=st2.ID and st2.OPTYPE='OB')
left outer join STATMENTS st3 on (st1.ID=st3.ID and st3.OPTYPE='CB')
group by st1.SDATE
having count(st2.OPTYPE)<>count(st3.OPTYPE);

this may turn out to be expencive (try it out) but it will give
you result with date in first field, numbers of 'OB' and 'CB' for
that date in second and third field for all dates that have numbers
of 'OB's and 'CB's unequal - meaning there are orphans.

Be carefull (you should implement some other type of parenting)
situation may arize where you have "nested" opens and closes
(I don't know if it migh pose a problem in your case) - meaning

in succession events are 'OB' 'OB' 'CB' 'CB' - (either 1-4, 2-3
combination or 1-3,2-4) will be treated as no orphans situation
by this query (it checks to see if there are different number of
'OB's and 'CB's on the same day).


Vladimir Vlaisavljevic

--- In firebird-support@yahoogroups.com, "Riho-Rene Ellermaa"
<r.ellermaa@h...> wrote:

> I have table called STATEMENTS where I have among other things 2

columns:

> SDATE DATE,
> OPTYPE VARCHAR(3)
>
> SDATE contains dates and OPTYPE contains various operation types

and 2 specific values - 'OB' (opening balance) and 'CB' (closing
balance). For each 'OB' value there must be also 'CB'. For each day
there may be more than one pair.

>
> I want to check if there exists some orphan 'OB' or 'CB' values

and rtrieve the dates of these cases. As my SQL knowledge is limited
to SELECT * FROM xxx :-) , I need some help.

>
> Is it possible to get the info with one SQL sentence?
>
>
>
> Riho-Rene Ellermaa
> senior programmer