Re: rownum HELP ....

Maybe I shouldn't have posted the SQL since I did it fast and there are some stuff that are obviously wrong.... But we need to get passed the synthax point...

So, let me try again (please disregard my wrong SQL):

Based on the data model I presented below, if the user passed as an input the division name ("DIVISION.division_nm"), I need to return all the VERSIONs per EACH REPORT in that DIVISION. Only the latest REPORT.max_display number of VERSIONs have to be returned per EACH report in that DIVISION. So if the DIVISION has 5 reports, and each report has N number of version in VERSION table, per each REPORT.max_display for that report, I need to only return REPORT.max_display number of versions per each report.

It sounds easy, but I just can't put my finger at it. The easy part would be to get all the versions per each report - you can join all the way through from DIVISION to VERSION. But the hard part is - how to limit number of VERSION rows returned based on REPORT.max_display field per each report for those versions?

Can you/anyone help...?

Darta

"Nic" <nicpayre[junk]_at_sympatico.ca> wrote in message news:<QHjVa.1620$Cx4.356733_at_news20.bellglobal.com>...
> "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 - 18:01:33 CEST