Need help with the following:
Have DIVISION, REPORT, DIVISION_REPORT and VERSION table.
Relationships are:
DIVISION.division_id = DIVISION_REPORT.division_id,
DIVISION_REPORT.report_id = REPORT.report_id and REPORT.report_id =
VERSION.report_id (and finally VERSION.version_id). So division can
have many reports and reports can be assigned to many divisions. Each
report can have many versions. REPORT table has "max_display" field to
be used to only return "max_display" VERSIONs per each report.
I want to retrieve a query so that it retrieves "max_display" VERSIONs
per each REPORT in a DIVISION. The user only passes a name of the
DIVISION.division_nm. So for each report found in a division, I want
to retrieve "max_display" versions. It does sound simple, but I have
tried many different ideas (all concentrated around 'rownum' and
subqueries to get this to work), but I just can't get it to work to
return "max_display" per EACH report in a DIVISION.
Here is what I have so far (other fields not described so far added
below- also since VERSIONs are numbered and only the latest need to be
returned (i.e. VERSION_NO desc)):
SELECT DIV.*, RPT.*, VERSION1.*
FROM DIVISION DIV, REPORT RPT, DIVISION_REPORT DIV_RPT,
(SELECT VRSN1.*
FROM REPORT RPT1, VERSION VRSN1,
(select * from VERSION order by REPORT_ID,
VERSION_NO desc) TEMP
WHERE RPT1.REPORT_ID = VRSN1.REPORT_ID
AND VRSN1.VERSION_ID = TEMP.VERSION_ID
AND rownum <= RPT1.MAX_DISPLAY) VERSION1
WHERE DIV.division_id = DIV_RPT.division_id
AND DIV_RPT.REPORT_ID = REPORT.REPORT_ID
AND REPORT.REPORT_ID = VERSION.REPORT_ID
AND upper(DIV.division_nm) = upper(S_PASSED_IN_APP_NAME)
ORDER BY DIV.sort_order, REPORT.sort_order, VERSION.roi_version desc
