Create a new account
This makes paging large data sets truly horrid, as you have to nest subselects at least three deep to have any sense of order because rownum is calculated before the ordering.
gah! MySQL is so much easier for day to day work!
Isn't that equivalent?
Yes, but what do you do for page two, when you want 50 < ROWNUM < 100?
This is much easier in MySQL.
We will probably move most this kind of SQL (large blocks) into Phrasebook classes, just doing an
execute_query('queryname',%args) or something.
If rownum was computed after the order by then it would be very useful - shame it isn't.
btw - we have about 8 O Reilly PL/SQL and Oracle books scattered around the desk of the dev team - I don't have time to read them tho, thats why have the TOAD jockies to keep the scary bad bits of oracle hidden in nice procedures and o
we have about 8 O Reilly PL/SQL and Oracle books scattered around the desk of the dev team
Which ones have you got? :D (I'm going on 14 physical books now plus the CD bookshelf, with two more on order.)
This place is starting to look like use PL/SQL; . Which isn't a bad thing, I suppose. ;)
I just have J P Harringtons, SQL Clearly explained and Halsall;s data Comms, Computer Networks, and open systems on my desk as well as the usual Perl books.
select rownum myrownum ,stufffrom mytablewhere myrownum>50 and myrownum<50
That's great, but it doesn't work.
select count(1) from words where word <'wordtofind'
I presume you've already done this [perl.org], since you mentioned nesting queries three levels deep.
Learn from how we do it in Perl. Got useful functionality that looks ugly, even hacking the language? Encapsulate it in a module. Abstract out the parameters you need (lower bound, upper bound, maybe table name and where clause if you want to use dynamic SQL), then stick it in a stored procedure.
Oracle PL/SQL Best Practices [oreilly.com] says:
Or, to generalize, encapsulate your queries in stored packages, procedures, and functions. Makes things reusable and, more importantly, nice and clean for you and your successors.
Reply to This
Hmm, even better, from the beginning of the chapter:
SQL-00: Establish and follow clear rules for how to write SQL in your application.
Okay, not all of that was relevant here, but the point was, once you've written the triple-nested select, encapsulate so from now
Get More Comments