use Perl

• Rownum? (Score:2, Informative)

  by claes (1758)

  SELECT * FROM myTable WHERE ROWNUM < 50;

  Isn't that equivalent?

  /claes
  • Re:Rownum? (Score:2)

    by jdavidb (1361)

    Yes, but what do you do for page two, when you want 50 < ROWNUM < 100?

    <g>

    --
    J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
    • Re:Rownum? (Score:1)

      by TeeJay (2309)

      exactly.

      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

      --
      
      @JAPH = qw(Hacker Perl Another Just);
      print reverse @JAPH;
      • Re:Rownum? (Score:2)

        by jdavidb (1361)

        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. ;)

        --
        J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
        • Re:Rownum? (Score:1)

          by TeeJay (2309)

          erm.. a load with ants and millipedes, A Practical Oracle 8i, the rest are buried under peoples workings out and schema diagrams.

          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.

          --
          
          @JAPH = qw(Hacker Perl Another Just);
          print reverse @JAPH;
    • Re:Rownum? (Score:1)

      by wesgf (6984)

      select
         rownum myrownum
         ,stuff
      from
         mytable
      where
         myrownum>50
         and myrownum<50
      • Re:Rownum? (Score:2)

        by jdavidb (1361)

        That's great, but it doesn't work.

        --
        J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
        • Re:Rownum? (Score:1)

          by wesgf (6984)

          select myrownum,stuff from(select (rownum) myrownum,stuff from mytable) where myrownum>50 and myrownum100 you're right, but this does.. not sure why it doesn't allow you to alias the rownum in the outer query
          • Re:Rownum? (Score:1)

            by wesgf (6984)

            the only reason I think the rownum concept has merit, is if you want to have a query on a table that is ordered by something other than the primary key - and you want to find a particular value, and view the rows around it.
            
            if there is an easy way to do that using limits and offsets let me know.. I'm trying to make a dictionary page that finds a word and prints nearby words (in mysql) - while reading up on it I found the oracle rownum stuff and decided it would be easier with that.
            
            I guess I could do a
            • Re:Rownum? (Score:1)

              by wesgf (6984)

              select count(1) from words where word <'wordtofind'

              ..
• Paging (Score:3, Informative)

  by jdavidb (1361) on 2003.02.13 15:45 (#17000) Homepage Journal

  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:

  • SQL-04: Put single-row fetches inside functions; never hardcode a query in your block.
  • SQL-06: Define multi-row cursors in packages so they can be used from multiple programs.

  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.

  --
  J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers

  Reply to This

  • Re:Paging (Score:3, Informative)

    by jdavidb (1361)

    Hmm, even better, from the beginning of the chapter:

    SQL-00: Establish and follow clear rules for how to write SQL in your application.

    • Never repeat a SQL statement.
    • Encapsulate all SQL statements behind a procedural interface (usually a package).
    • Write your code assuming that the underlying data structures will change.
    • Take advantage of PL/SQL-specific enhancements for SQL.

    Okay, not all of that was relevant here, but the point was, once you've written the triple-nested select, encapsulate so from now

    --
    J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
• ROW_NUMBER() (Score:1)

  by runrig (3385)

  A little late to this thread...but Oracle has support for this with "ROW_NUMBER() OVER..." (along with other useful "Analytic Functions" involving "...OVER...").