i know i could put a grid of owners/plants date’s grouped by YYYY or YYYY/MM  then  have a detail grid that is the counts that i want using the master grid’s PlantKey + date-group.

 

But that’s 2 grids.

 

somehow I need to join those two queries.  

I need a master-detail query done in a single SQL statement joining the master and the detail queries in a single result.

 

here’s the master query (for equipment since 2010):

 

select  o.ownername, p.location, substring( r.datetested from 1 for 4 ) as RVYear, count(1) as RV_Event_Count
from plants p, reliefd r
join owners o on p.ownerkey = o.uniquekey
where r.plantkey = p.uniquekey
and
r.datetested=coalesce(r.datetested, '', null, ' ' )
and
r.datetested > '2010'
group by o.ownername, p.location, rvyear
order by
rv_Event_count desc,
o.ownername

 

now, each of those rows?

 

i want these details...

 

this is the detail query:

 

select o.ownername, p.location, (select count(1) from reliefd r where r.plantkey=p.uniquekey) RV_Event_count,
(select count(1) from reliefd r where r.plantkey=p.uniquekey and ( (upper(substring( r.manufacturer from 1 for 4))='CONS') or (upper(substring( r.manufacturer from 1 for 4))='DRES') )) RV_Cons_count,
(select count(1) from cv c where c.plantkey=p.uniquekey and c.mostrecent='T') cV_event_count,
(select count(1) from cv c where c.plantkey=p.uniquekey and upper(substring( c.manufacturer from 1 for 4))='MASO' ) CV_mason_count
from owners o, plants p
where p.ownerkey = o.uniquekey

 

------ in here i’d need the row i’m on from the master query ----

and

(detail)p.location = :(master.p.location) and

(detail).substring( r.datetested from 1 for 4 ) = :(master.p.RVYear)

order by
rv_event_count desc,