Mark Wong wrote:
> On Mon, 2002-02-11 at 15:52, Mark Wong wrote:
> > On Thu, 2002-02-07 at 22:42, Zabach, Elke wrote:
> > > Mark Wong wrote:
> > >
> > > > I don't think anyone asked this variation (or my eyes are
> > > > getting tired
> > > > from searching.)
> > > >
> > > > What if we want the result of the first 10 rows to be in a
> > > > subquery? Is
> > > > there a way to use fetch to do that? For example (using TOP)
> > > > I want to
> > > > see which customers made the most recent 1000 orders:
> > > >
> > > > SELECT DISTINCT order_customer_id, customer_name
> > > > FROM (SELECT TOP 1000 order_id, order_customer_id
> > > > FROM order_table
> > > > ORDER BY order_date DESC), customer_table
> > > >
> > > > Or am I already in trouble because we can't use an ORDER BY
> > > > clause in a
> > > > subquery? I hope I did that example correctly. :-)
> > >
> > > 1. Yes, you are in trouble, because ORDER BY will not
> work in a subquery.
> > > 2. All those mails written in this list concerning 'the
> first x rows AFTER
> > > ORDERING'
> > > are true for your example
> > > 3. perhaps this is a chance for you:
> > >
> > > declare myresult cursor for
> > > SELECT order_id, order_customer_id
> > > FROM order_table
> > > ORDER BY order_date DESC
> > >
> > > SELECT DISTINCT order_customer_id, customer_name
> > > FROM (select * from myresult where rowno <= 1000),
> customer_table
> > >
> > > CLOSE myresult
> > > handle the real result
> > >
> > > Elke
> > > SAP Labs Berlin
> >
> > That works when I do something like 3. by hand with dbmcli, but not
> > loading a command file using repmcli. I'll give a
> (hopefully) simple
> > example that might not be the most interesting example:
> >
> > CREATE DBPROC test AS
> > BEGIN
> > DECLARE recent_orders CURSOR FOR
> > SELECT order_id
> > FROM test.orders
> > ORDER BY order_date DESC;
> > DECLARE item_list CURSOR FOR
> > SELECT item_id, COUNT(item_id) AS qty
> > FROM (SELECT * FROM recent_orders WHERE rowno <= 1000)
> > GROUP BY item_id;
> > END;;
> >
> > I get this error: SQL error -8031 = Owner must be specified
> >
> > I assume it wants some kind of owner with the subquery.
> Perhaps there
> > is another way to try to load this?
> I meant to say that I assume it wants an owner with the
> temporary table
> (?) recent_orders, but if I attempt to put test.recent_orders, then I
> get the following error: SQL error -4004 = Unknown table
> name:RECENT_ORDERS
1. in recent_orders there is exactly one column named order_id.
you will have no chance to select / group by a column named item_id.
This is not part of recent_orders.
Let's assume that item_id is part of resent_orders for the next step
2. test.recent_orders is not possible (by now) to access to resultset
prepared before. This is a missing feature which will be added with the
next kernel version
3. For the current kernel version I do not know how to solve your problem,
sorry.
Mmmh, that is not really true, I have, but it looks strange:
create somewhere outside your dbproc a table (assuming that you only
need
order_id and item_id (or only need item_id but need something to make
the primary key
of this table unique). 'the_datediff' is mandatory (not the name, but
that integer as
first column of the primary key)
create table recent_orders (
the_datediff int,
order_id int,
item_id int,
primary key (the_datediff,order_id,item_id))
in your dbproc:
insert test.recent_orders
select datediff (date, order_date), order_id, item_id
from test.orders;
DECLARE item_list CURSOR FOR
SELECT item_id, COUNT(item_id) AS qty
FROM (SELECT * FROM test.recent_orders WHERE rowno <= 1000)
GROUP BY item_id;
delete test.recent_orders;
the trick is the datediff (always positive, the newest dates have the
smallest values).
If the value is stored as first primary-key-column the values are sorted
according
from newest to oldest.
in the from select there is no other chance than to scan the table
recent_orders
(from newset to oldest) and finish scanning after 1000 resultrows.
As far as I understood, this is, what you wanted to do, isn't it?
No nobel-prize expected for this, but a possible solution.
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
