Master Detail with Having Clause

rbuser

I'm searching for obsolete inventory items by joining the inventory
(master) to the one-to-many transaction (detail) table that has a
grouping function where the max transaction date is older than 5 years
OR no transactions exist at all.

The transactional group-by with having condition works great when there
are some older transactions out there that fulfill the having clause
where the max date > 5 years old.

I would also like to include the master row(s) when NO detail
transactions exist. If I include all masters on the SQL link I get
everything, I just want the master rows that have OLD transactions OR no
transactions at.

Thanks - RB Server 12 / Oracle 11g / Delphi 2007

rbuser

I think I'm stuck on trying to implement an Oracle where clause for NOT
EXISTS on the detail table.

Is there way to check for a NOT EXISTS condition on a report / subreport?

TIA - Jon Gray

nicocizik

Hi Jon,

You need to somehow pre-process the master table to only show records
with details dating before 5 years ago or that have no details.

You might be able to accomplish this by joining the master table with
the detail table so you can filter out any unneeded master records while
still retaining all necessary master data. Then you could link the
joined master table to the detail table and show all, knowing that only
the master records you need are present.

rbuser

Thanks Nico.

I'm stuck on trying to create a master detail report and only show
masters that have no associated detail.

A subreport would work for me if there is a way to do the WHERE NOT
EXISTS in Report Builder. Do you know of any way?

nicocizik

Hi Jon,

This requirement is best handled in SQL code rather than inside the report.

Consider the following two tables.

Master Table
ID Name
-- ----
1 John
2 Paul
3 George
4 Ringo

Detail Table
DetailID MasterID Date
-------- -------- ----
1 1 1989-01-01
2 1 1999-01-01
3 1 2009-01-01
4 2 1999-01-01
5 4 2009-01-01
6 4 2009-01-01

So if we properly filter our Master table with your specifications, we
want something like the following...

1 John (Contains details with dates < 5 years ago)
2 Paul (Contains details with dates < 5 years ago)
3 George (Contains no details)

The SQL code below accomplishes this using an Outer Join of the master
and detail tables and selecting only distinct master fields. This SQL
was generated and tested in DADE.

SELECT DISTINCT MasterTable.ID, MasterTable.Name
FROM MasterTable MasterTable
FULL OUTER JOIN DetailTable DetailTable ON
(DetailTable.MasterID = MasterTable.ID)
WHERE
( DetailTable.Date < CONVERT(DATETIME,'2008-01-01',120) )
OR ( DetailTable.DetailID IS NULL )

Now that you have the Master Table filtered the way you need, you can
link it to the Detail table and filter the detail table on the correct
date (making sure all masters are displayed even if they have no details).