This topic has been archived. It cannot be replied.
-
工作学习 / 专业知识杂谈 / 看似简单实不容易的UNIDATA QUERY，折腾了一个星期，还没有结果。有两个table,一个Order table:
OrderId, ProductId,QTY,DateOfOrder
1,AB-11,100,4/1/2006
2,AB-11,300,6/1/2006
3,AB-11,100,7/8/2006
4，AB-21，100，3/2/2006
另一个是 ProductPrice table.
ProductId,PriceEffDate,Price
AB-11,3/8/2006,2
AB-11,5/8/2006,3
AB-11,7/9/2006,3.1
AB-21，1/1/2006，4
我需要知道每一ORDER的OrderID,ProductID,QTY,PRICE
我查过，UNIDATA 里面没有 select TOP #,好像也没有stored procedure.
请SQL 高手赐教。(顺便探访坛中是否深藏IBM UNIDATA高手)
-sunday8(sunday8);
2006-5-20
{463}
(#2982384@0)
-
瞎想：unidata支持rowid（不是物理的，是查询输出的连续id）吗？ 如果有，你可以用 where rowid < 10 来做top 10.否则，从理论上讲,用纯标准SQL(80??)，top N,需要N个子查询。
-647i(步行万里);
2006-5-20
{57}
(#2983339@0)
-
好像不可以。Unidata资料奇少。 不过楼下的那位高人可以从subquery中提取TOP 1。问题得以解决。感谢支持。
-sunday8(sunday8);
2006-5-21
(#2983540@0)
-
跟我说的一样，不过performance 极差。要查个top10不光效率极低，光写sql就会写个半篇纸了。
-647i(步行万里);
2006-5-24
(#2989915@0)
-
same as subquery (below).. when I run the query, it halts the main-frame.. :(
but at least there is a solution.. (run after work :) hard to find reference to improve any performance on those legacy system..
-sunday8(sunday8);
2006-5-31
(#3002356@0)
-
You need to make sure proper index is defined on ORDER and PRODUCTPRICE.If you connect to DB2 LUW for zOS, you can use the following:
select orderid, productid, qty, price
from
(select orderid, o.productid, qty, price
ROW_NUMBER () OVER (PARTITION BY orderid ORDER BY pp.priceeffdate DESC ) AS row_number
from order o, productprice pp
where o.productid = pp.productid and o.dateoforder >= pp.priceeffdate) as op where row_number=1
;
If you use DB2 for zOS, you have to tolerate the whole table scan of ORDER if you haven't defined index on (orderid, productid, qty).
-886xyz(cqcq);
2006-5-31
{507}
(#3003369@0)
-
but ROW_NUMBER/PARTITION are not key words in unidata, the query wouldn't work..
Than you.
-sunday8(sunday8);
2006-6-5
(#3011120@0)
-
.select orderid, productid, qty, price from order o, productprice pp
where o.productid = pp.productid and o.dateoforder >= pp.priceeffdate and pp.priceeffdate =
select max(priceeffdate) from productprice ppp where pp.productid = ppp.productid
-886xyz(cqcq);
2006-5-21
{245}
(#2983477@0)
-
Thank you for your hints..it actually works perfectly...with a minor condition added in sub query..
select orderid, productid, qty, price from order o, productprice pp
where o.productid = pp.productid and o.dateoforder >= pp.priceeffdate and pp.priceeffdate =
(select max(priceeffdate) from productprice ppp where pp.productid = ppp.productid and
and o.dateoforder >= ppp.priceeffdate)
Thank you!
-sunday8(sunday8);
2006-5-21
{351}
(#2983528@0)
-
I found a better solution. DTS to SQL Server and schedule it run at night.
Life is much easier.. :)
-sunday8(sunday8);
2006-6-13
(#3027566@0)