Re: rownum HELP ....

"Nic" <nicpayre[junk]_at_sympatico.ca> wrote in message news:eujVa.1600$Cx4.354518_at_news20.bellglobal.com...
>
> "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 = ...
>
3 - Also you should be carefull with cartesian joins. When you you have multiple tables in FROM clause of your query, if one of the tables isn't join with the others in the WHERE clause, Oracle will perform a cartesian join. That mean that every rows of table A is join with every rows of table B and the result is quite big...and often not what you have expected.

>
> >
> > Darta
>
>
Received on Tue Jul 29 2003 - 06:00:46 CEST