RE: embedded SQL Precompiler

["M. Lazarus" <mlazarus@xxxxxxxx>]

Joe,

At 2/20/02 10:18 PM, you wrote:
> >   As compared to what?  The rest of the SQL world?  PLENTY!  A couple of
> > examples:  How would you retrieve the top 10 of a list?  SQL Server and
> > Oracle (I believe) have TOP XX.  How about ROWNUM, which would allow me to
> > retrieve, say, rows 50-100?  This is on V4.5, so my apologies to Rochester
> > if these have been added in 5.1.



>Don't shoot me for asking, but how often do you retrieve an arbitrary number
>of rows starting somewhere in the middle of a view?  Isn't that what a
>scrollable cursor is for?  And I thought there was the ability to get the
>first NN rows, but I could be wrong.  I'm no SQL expert.

  I'm not an expert either, so some of my gripes may be in there, but I
might not know about it.  Some of the techies in my shop know SQL Server
and Oracle well and they might email me a simple solution to a situation
and I try to run it under ISQL and it won't accept it.

  I did see in a DB2 UDB manual that FETCH FOR n ROWS is valid, but ISQL
won't accept that.  I don't want to create a program to run an ad hoc query.

  I actually have a complex report to create.  One of the rows will
represent a "Top 50 total" value.  I also wanted to be able to easily add a
"Next 50 total" row.

>1. Create a view of, say, invoices that were over 90 days, by customer and
>date
>2. Subselect out just the customer numbers from that set, showing them to
>the user
>3. Let the user pick a customer, and go back to the original result set and
>then subselect for just that customer
>
>THIS would be power.  THIS would be a real boon to programming.  Sure, you
>can do it by just re-running the SELECT over the whole file, but if the
>selection criteria has any complexity to it, that's a lot of churning
>against the database, when you already have the data selected from the
>first pass.

  I agree that this would be a real performance booster for large / complex
tables.

  -mark