Re: IDS Feature Request List (including potential new requests).

bozon wrote: > Oh, I thought you were against both. Sorry about that. Still it seems > like min((tuple)) is a natural consequence of the first feature, > because essentially min is a short-cut for "compare" all of the values > and give me the lowest one. But now you need to get into the world of passing back tuples as column values to the client or otherwise unnesting the tuple. You can't just run a fucntion (MIN) and have it spit out columns. It has to spit out a scalar, a row/tuple or a table. Not clear how far IDS's tuple support goes... >>serge > > That is the one I do have a problem with because there are well known > constructs that do the job. > >>end serge > > > Nothing I see that is as elegant as integrating a tuple more closely > with the language by allowing it to be used in more places where in the > past you could only use a scalar value. Plain scary to me.. What's next a request for nested tables? >>serge > > By your argument 60% of all Informix customesr bastardize SQL because > they use non ANSI databases. > >>end serge > >>serge > > How is an operation that truncates a set to the n-smallest/biggest > tuples according to some rule non relational? It takes an unordered set > of tuples and it returns an unordered set of tupples. > >>end serge > Let me try to explain what I mean by this. Partly because it is > implemented differently in every SQL (see a post by Model-Bosch, Tilman > which explains one problem > http://groups.google.com/group/comp.databases.informix/browse_frm/thread/0de5ebbc0ff2a8b2?hl=en). > Another thing that I find wrong with it is that it is needlessly > limited because it isn't well integrated in most SQL's, such as in > Informix where you can't put it in a subselect. Aha! OK. Now we're getting to teh meat of the business. Instead of asking for a brand new feature wouldn't it be logical to further orthogonalization (is that a word??). ORDER BY is a standardized construct, its'just not enabled everywhere. And it is easy to convince any vendor that some sort of TOP/FIRST fucntionality is needed since next to all have it. So why not standardize a shared syntax and semantics (cheaper to implement too). Finding obvious exensions to existing structures as extending the SQL Standard that way is common practice. > > <Putting on my abestos underwear> I actually prefer the way Oracle does > this by having a virtual column. This integrates well because you have > the full power of the language to operate on this column. If I only > want to bring back the odd rows from a selection I can. I can basically > do any comparison against row_number (as I think it is called) that my > little brain can imagine. Of course having a virtual column is a hack > but it works well because it is more orthoganal than "select first" > constructs seem to be. Can you bring back only the odd records with > DB2's construct? > <Taking off my abestos underwear, I begin enjoying the nicely toasted > marshmellows I thoughtfully put in my pockets before being inevitably > flamed for saying something nice about Oracle on c.d.i.> Not for saying something nice about Oracle, but... have you taken a look at teh limitations of rownum? It works if and only if teh query is pretty trivial and the predicate is just right and it's Sunday afternoon.... Now THAT is a kludge if I have ever seen one. The better way (which works in any context, is standardized and supported by multiple vendors (and maybe IDS in the future) is ROW_NUMBER() OVER(<partitioning> <ordering>). Here are your odd rows. Use BETWEEN for FIRST with OFFSET (mySQL) SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY c1) AS rn, t.* FROM T) AS x WHERE rn / 2 * 2 <> rn But let me try to reach some sort of a conclusion here: I think it is great that this group is collecting feature requests. From a development point of view it is even more beneficial to get "business problems". E.g. "I need a way to page through a resultset without holding a connection" That way Development can look into the most natural and general "solution". E.g. tuple comparison, global cursor ID, abstract rownumbers... Cheers Serge -- Serge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab

"I think it is great that this group is collecting feature requests. From a development point of view it is even more beneficial to get "business problems". " Yes. I was hoping most stuff would be obvious feature requests e.g. truncate table that was only added in 10.00.UC4! All the stuff that is fairly simple to understand/implement and we could perhaps we into IDS v.Next The one thing I haven't mentioned is that we will probably need more than one person to 'vote' for a given feature...