Re: Why I too don't like the OCP

Jeff,

I loved your answer. It is funny. Now the serious answer is that rownum between 10 and 20 is always gonna return nothing since rownum = 1 is the first row of... the results. It's like wanting a building starts in the 10th floor.

jeff_at_work.com (Jeff) wrote in message news:<aaosn6$qpk$1_at_cronkite.cc.uga.edu>...
> In article <3CCEBCBE.81E0812_at_exesolutions.com>, Daniel Morgan <dmorgan_at_exesolutions.com> wrote:
> >Jeff wrote:
> >
> >> >Unless of course they are asked to go to code reviews or to deal with
> queries
> >> > that don't
> >> >work because they are presumed to be the experts.
> >> >
> >> >I met a DBA asked to solve a problem with a query that looked like this:
> >> >
> >> >SELECT *
> >> >FROM mytable
> >> >WHERE rownum BETWEEN 10 AND 20;
> >> >
> >> >Guess what happened?
> >> >
> >> >Daniel Morgan
> >> >
> >>
> >> Uhhhmmm...
> >> He saw rownum wasn't a column in mytable and tried adding it?
> >>
> >> He added "AND rownum IS NOT NULL" to the WHERE clause, " just in case"?
> >>
> >> He quit his job, had a sex-change, and became a nun in France?
> >>
> >> She was furious that you (you SEXIST PIG!!) automatically assumed that SHE
> was
> >> a "HE"?
> >>
> >> She reckoned that rownum is meaningless without the context of a query and
> its
> >> resultset?
> >>
> >> Guessing games are FUN! :-)
> >
> >Based on your answer, I would suggest that you try the query against any table
> > you have with more
> >than 20 rows of data in it.
> >
> >It is an excellent reason why good DBAs must understand more than just the
> > straight-forward DBA
> >stuff like install, configure, backup, and restore.
> >
> >Daniel Morgan
>
> I'm not sure what you mean about "my answer," since most of it was
> obvious humor... or so I thought. :-/
>
> I'm fairly confident of what rownum is, how it works, and what'll happen with
> this query. Rownum is both a counter AND a logical column in the resultset--
> two things referred to by one name--the column is assigned an incrementing
> value (the counter) starting with 1 at the time the row is selected (before
> any sorting or grouping). Since there are no rows in the resultset at the
> beginning of a query, rownum (the column) is both valueless and meaningless
> and cannot be used this way in the WHERE clause of a query. There will never
> be a case where rownum (the counter) satisfies the above WHERE clause, since
> it MUST start with 1, so setting a lower boundary is pointless. Therefore,
> your query is pointless--it'll always return no rows because it either assumes
> rownum has meaning that it doesn't (yet) or it checks for something that will
> never happen.
>
> A way to accomplish what this query seems to want is to give rownum (the
> column) meaning by doing a sub-query first that doesn't set a lower boundary
> on rownum (the counter) and then using rownum (the column) for setting the
> lower boundary in the outer query.
>
> Like so:
> SELECT ...
> FROM (SELECT rownum rn, ...
> FROM mytable
> WHERE rownum <= 20)
> WHERE rn >= 10;
>
> Does this pass your mustard, Dan????
Received on Thu May 09 2002 - 13:58:47 CDT