EA 6.1.1.2 Query Builder enhancements

Just tried using the query builder to construct a query which defeated me yesterday using OpenOffice.org. In each case it was much easier to write it in SQL (but I am looking for something for novices to use to build their own queries) The query was like this (SQL copied and slightly tidied from query builder) SELECT M.Company, M.Area, M.Started, M.Ended FROM Table.Members M WHERE M.Area = 'GS' AND ( ( M.Started .GE. '2007-04-01' AND M.Started .LT. '2008-04-01' ) OR ( ( M.Ended .GE. '2007-04-01' AND M.Ended .LT. '2008-04-01' ) ) ) Because of your forum s/w I have used textual symbols for the comparison operators 'greater than or equal to' and 'less than' This is MySQL, yes, I know about the between operator! Specific difficulties I had building this: 1) If I did not quote the dates then the query was executed with a bad date value (which did not generate an error message). 2) Developing this query without first writing it down was very difficult (and if I am going to write it down first why don't I just write it in SQL) What would make (2) easier is to be able to promote/demote query terms in or out of compound operations perhaps by dragging the labels or by use of arrow keys. Incidentally if I do this query 'properly' then I get the following SQL (copied verbatim, note the stray newline), which it seems to me could do with brackets around the 2 'OR' clauses to make it clearer SELECT M.Company, M.Area, M.Started, M.Ended FROM Table.Members M WHERE M.Area = 'GS' AND ( M.Started BETWEEN '2007-04-01' AND '2008-04-01' OR M.Ended BETWEEN '2007-04-01' AND '2008-04-01' ) ; e.g. something like the first example (M.Started BETWEEN '2007-04-01' AND '2008-04-01') OR (M.Ended BETWEEN '2007-04-01' AND '2008-04-01') Thanks for a great product. Ed

Just to clarify a bit, if I am writing a query in an SQL editor I can promote or demote clauses by putting brackets around the region and adding AND or OR, or I can delete the brackets and operator. In query builder I have to re-enter everything that I wish to promote or demote. Ed Edited by: Ed on 19-Apr-2008 15:58

> {quote:title=Ed wrote:}{quote} > [...] > 1) If I did not quote the dates then the query was executed with a bad date value (which did not generate an error message). This will be improved so that common date literals are recognized and quoted. > > 2) Developing this query without first writing it down was very difficult (and if I am going to write it down first why don't I just write it in SQL) > > What would make (2) easier is to be able to promote/demote query terms in or out of compound operations perhaps by dragging the labels or by use of arrow keys. I agree that something along those lines would be very helpful, but I have not yet looked at what it would take to implement. I'll add it to our Feature Request list. > > Incidentally if I do this query 'properly' then I get the following SQL (copied verbatim, note the stray newline), which it seems to me could do with brackets around the 2 'OR' clauses to make it clearer > > SELECT > M.Company, > M.Area, > M.Started, > M.Ended > FROM > Table.Members M > WHERE > M.Area = 'GS' > AND > ( > M.Started BETWEEN '2007-04-01' > AND '2008-04-01' > OR M.Ended BETWEEN '2007-04-01' AND '2008-04-01' > ) > ; > > e.g. something like the first example > > (M.Started BETWEEN '2007-04-01' AND '2008-04-01') > OR > (M.Ended BETWEEN '2007-04-01' AND '2008-04-01') Parenthesis are used around compound statement, so you can get it the way you want it by setting the top-level condition to "Any" and then make each BETWEEN condition a compound statement. > [...] Kind Regards, Hans