Hi,

With (a) I need to get a count of the number of relatives which belong to
the member ie

John Smith Sally Smith 2
John Smith Jack Smith 2
I tried using the inline select, but get a count of all relatives of type
37.


a)
select m.firstname, m.surname, p.firstname, p.surname,
(select count(c.memberid)as tot from coveredmem where c.relativetypeid =
37 and c.memberid= e.memberid and c.optionid = e.optionid)

from member m
join memberinfund e on (m.memberid = e.memberid)
join coveredmem c on (c.memberid = e.memberid) and (c.optionid =
e.optionid)
join person p on (p.personid = c.personid)

where c.relativetypeid = 37


With (b) I need an AGE function in order to determine a members age, given
the date of birth - is a useful UDF for this?
b)
select m.firstname, m.surname, p.firstname, p.surname, r.name

from member m
join memberinfund e on (m.memberid = e.memberid)
join coveredmem c on (c.memberid = e.memberid) and (c.optionid =
e.optionid)
join person p on (p.personid = c.personid)
join relativetype r on (r.relativetypeid = c.relativetypeid)

where (c.relativetypeid in (31,32,33,34)) and ((60 < AGE(DOB)??????


Many thanks,
Dion.


******************************************************************************
Hierdie e-pos het geprivilegieerde inligting wat uitsluitlik vir die
geadresseerde bedoel is. Indien u nie die bedoelde ontvanger
is nie, mag u die inhoud hiervan nie openbaar maak, kopieer
of in enige opsig publiseer nie. Die inhoud hiervan is onderhewig
aan outeursreg. Indien u hierdie e-pos per abuis ontvang, geliewe
dit uit te wis.
This e-mail contains privileged information which is exclusively
intended for the addressee. If you are not the intended addressee,
you may not disclose, copy or in any other way publish the
contents hereof which is subject to copyright. If you receive this
e-mail in error, please delete it.
******************************************************************************



[Non-text portions of this message have been removed]