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

> -----Ursprüngliche Nachricht----- > Von: informix-list-bounces@iiug.org > [mailto:informix-list-bounces@iiug.org] Im Auftrag von bozon > Gesendet: 09 January 2006 15:27 > An: informix-list@iiug.org > Betreff: Re: IDS Feature Request List (including potential > new requests). > .... > > select first 1 > * > from > employee > where > company_id = ? and > ( last_name > ? or > ( last_name = ? and > ( first_name > ? or > ( first_name = ? and > ( MI > ? or > ( MI = ? and > ( suffix > ? or > ( suffix = ? and uid > ? ) > ) > ) > ) > ) > ) > ) > ) > order by > company_id, last_name, first_name, MI, suffix, uid> ; > > ... > I may even have gotten the SQL wrong in the first case which > is part of > my point. Whether or not it is wrong depends on what you want to read, but I suspect your real intention would be covered by this SQL statment: select first 1 * from employee where company_id = ? and (( last_name > ? ) or ( last_name = ? and first_name > ? ) or ( first_name = ? and and first_name = ? and MI > ? ) or ( first_name = ? and and first_name = ? and MI = ? and suffix > ?) or ( first_name = ? and and first_name = ? and MI = ? and suffix = ? and uid > ? ) ) order by company_id, last_name, first_name, MI, suffix, uid ; > > I can't believe that this query doesn't come up very often in > applications. > At least this type of problem will be common, I guess. However, I am not sure, whether the syntax ...WHERE and (last_name, first_name, MI, suffix, uid) > (?,?,?,?,?) is easier to read and more intuitive. This synatx may as well suggest to search for all rows where all column values are greater than the specified value .

Reply to Model-Bosch, Tilman I think our SQL are equivalent. I must admit that your SQL reads better but I think if I remember my boolean algebra correctly they are the same. I just thought that I might have a typo in it because there is so much nested or'ing and and'ing going on, removing that is what makes your SQL easier to read. I wonder which one the Optimizer likes better. Here is a quick demonstration that they are the same Substitute LG = Last_name Greater than, LE = last_name equal to etc. LG or ( LE and (FG or (FE and ( MG or ( ME and ( SG or ( SE and UG ) ) ) ) ) ) ) distribute ME: LG or ( LE and (FG or (FE and ( MG or ( ME and SG or ( ME and SE and UG ) ) ) ) ) ) distribute FE: LG or ( LE and (FG or ( FE and MG or ( FE and ME and SG or ( FE and ME and SE and UG ) ) ) ) ) distribute LE: LG or (LE and FG or ( LE and FE and MG or ( LE and FE and ME and SG or ( LE and FE and ME and SE and UG ) ) ) ) using the Boolean commutative property: a + (b + c) = a + b + c where + is or means b and c. In our case we have a + (b + (c + (d) ) ) ). LG or (LE and FG) or (LE and FE and MG) or (LE and FE and ME and SG) or (LE and FE and ME and SE and UG) Which is what you have: (( last_name > ? ) or ( last_name = ? and first_name > ? ) or ( first_name = ? and and first_name = ? and MI > ? ) or ( first_name = ? and and first_name = ? and MI = ? and suffix > ?) or ( first_name = ? and and first_name = ? and MI = ? and suffix = ? and uid > ? ) ) If you read them logicaly they also seem to match. >> However, I am not sure, whether the syntax >> ...WHERE and (last_name, first_name, MI, suffix, uid) > (?,?,?,?,?) >> is easier to read and more intuitive. good point. I am pretty sure that the standard defines tuple comparison the way that I have defined it. I of course may be wrong and I don't have a copy of the standard in front of me. Thanks