On Tue, 2002-02-12 at 03:09, Zabach, Elke wrote: > 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
Oops, my bad, I was trying to dumb down my query. Looks like I broke it at the same time. > 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 > Would you be able to offer an ETA of when the next kernel version will be out? I'm trying to do some performance work and I believe this will be a significant factor. > 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 Yeah, I'm not too excited about that solution. So I'm hoping the next kernel version is coming out soon. Thanks, Mark _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
