Re: rownum HELP ....

"Darta" <tvesel_at_hotmail.com> wrote in message news:dbc964ae.0307281319.76315928_at_posting.google.com...
> 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
>
>
> But this is not working since it does not work properly per each
> report; but rather subquery with rownum eliminates some versions that
> are valid. I think I know what is wrong but I can't find a
> solution......

I think i know too... it's all wrong!!! Sorry my friend ;-)

Seriously, i can't help you much but here a couple of hints...

1 - Since you use alias in the top level query, you HAVE to use them in the WHERE clauses.
> FROM DIVISION DIV, REPORT RPT, DIVISION_REPORT DIV_RPT,
but your trying to reference the table REPORT with REPORT.REPORT_ID

2 - You're using the ROWNUM inside the inline-view... that's wrong since the ROWNUM pseudo-column is create
at the same time the inline-view is. Oracle append seqential number to each row is retriving from the DB, so the rownum cannot appear in the WHERE clause of the query, only in the upper-level query. Eg,

• the use rownum in that query has no meaning...
• try it with rownum = 2 -- select tname, rownum from tab where rownum = 1; --
• this one make more sense -- select a.* from (select * from tab) a where a.rownum = ...

>
> Darta
Received on Tue Jul 29 2003 - 05:46:16 CEST