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

bozon wrote: > One last request related to tuples allow tuples to be treated like > built ins so that they can be parameters to min(...), max(...), > count(...), count(distinct ...), etc. Treating them this way allows for > some very useful SQL to be written clearly for example > > select > min(last_name, first_name, MI, UID) > from > employee > where > (last_name, first_name, MI, UID) > ("Bozon", "Utah", "Carl", 1023) > ; SELECT last_name, first_name, MI, UID FROM employee WHERE (last_name, first_name, MI, UID) > ("Bozon", "Utah", "Carl", 1023) ORDER BY last_name, first_name, MI, UID FETCH FIRST ROW ONLY (or use TOP, ROWNUM or whataver the DBMS supports to limit rows) Cheers Serge -- Serge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab

In the hopes of improving my OTPR (On Topic Posting Ratio): >Serge Said >SELECT last_name, first_name, MI, UID > FROM employee > WHERE (last_name, first_name, MI, UID) > ("Bozon", "Utah", "Carl", 1023) > ORDER BY last_name, first_name, MI, UID > FETCH FIRST ROW ONLY > (or use TOP, ROWNUM or whataver the DBMS supports to limit rows) > Cheers > Serge Thank You, that is in one way around it, but does this work when the min is in the subselect such as: select * from employee_plans ep where employee_id = ? and (plan_type, effective_date ) = (select min(plan_type,effective_date) from employee_plans ep_s where ep_s.employee_id = ep.employee_id and termination_date >= today ) ; I don't believe that it is allowed in Informix. Nor do I believe it is allowed in DB2. Also, does the engine actually do the "order by" and throw away the rest of the data. It seems like in many cases it would. In my SQL it seems like the optimizer knows that you only want the minimum value and if there was a compound index could use it or could just scan the data for that employee for the minimum which would be faster than sorting the data for that employee and then throwing away all of the records except the first. The above query gives you the current effective plan for today of the first type of plan excluding any future plans . This is somewhat contrived because I wouldn't normally just want to see the first plan type. I have run into this construct before in reporting queries at my current company. Although, I don't have any of the queries in front of me (and I am sure they are proprietary ;-) ). The work around we use is of course to concatenate things together in the min(...). Furthermore, I always think of "fetch first row only" and constructs like it, as hacks (concatenating in the min is definitely a hack) that are work arounds of limitations in "TRUE" SQL. When ever I see them or use them, I am suspiscious of two things: that the developer doesn't know what he/she is doing, and/or there really is something hard or impossible to express in standard SQL. This is when I start thinking of enhancements. In this case "fetch current valid plan excluding future plans" is something hard to express in SQL. Can others come up with queries that tuple comparisons and tuples in aggreate functions would be helpful or is it just me? Thanks Curtis "Bozon" Crowson

Bozon Said ... (plan_type, effective_date ) = (select min(plan_type,effective_date) from employee_plans ep_s where ep_s.employee_id = ep.employee_id and termination_date >= today ) ; I guess the proper syntax for tuple min would be min((plan_type,effective_date)) I know the double parens are somewhat obnoxious but I does convey that (plan_type, effective_date) is to be compared as a tuple. I just realized that tuple isn't an everyday word. >From dictionary.com tuple - In functional languages, a data object containing two or more components. Also known as a product type or pair, triple, quad, etc. Tuples of different sizes have different types, in contrast to lists where the type is independent of the length. The components of a tuple may be of different types whereas all elements of a list have the same type.