Description: I would like to be able to generate a query like the following :- SELECT * FROM "Table1" WHERE ( "FieldA" = :AskA OR "FieldA" IS NULL) AND ("FieldB" = :AskB OR "FieldB" IS NULL ) This could be by the use of brackets (as in the SQL)or some marker in the column Steps to Reproduce: 1.Click on Query 2.Click on Query in Design View 3.Enter as per attached screen shot 4.Click on switch design view on/off Actual Results: It's an enhancement request. Expected Results: SELECT * FROM "Table1" WHERE ( "FieldA" = :AskA OR "FieldA" IS NULL) AND ("FieldB" = :AskB OR "FieldB" IS NULL ) Reproducible: Always User Profile Reset: No Additional Info: After encountering the end bracket continue parsing at the row of the open bracket and the column to the right of it.
Created attachment 162074 [details] Screen shot of proposed query
@Trevor: Does this work in the way you want if you remove the parameterized part of the query and replace it with a value to search for directly against the field ? Does your query work in the SQL direct edit mode (I'm assuming/hoping it does) ? If it works in these situations, then the problem would appear to lie in correctly parsing the parameterized query aspect within the UI.
The query works in SQL direct edit. Using values rather than parameters gives the following SELECT "Table1".*, "Table1"."Field1A", "Table1"."Field1B" FROM "Table1" WHERE ( "Field1A" = '101A' AND "Field1B" = '105B' OR "Field1A" IS NULL AND "Field1B" IS NULL ) This is not what I was hoping to find.
[Automated Action] NeedInfo-To-Unconfirmed
@Treveor : isn't this a DUP of the other bug reports you filed
Yes, sorry. I should have changed the status when I did the other one.
This is a feature request. The GUI couldn't create such a code. It creates a code like SELECT * FROM "Table1" WHERE "FieldA" = :AskA AND "FieldB" = :AskB OR "FieldA" = :AskA AND "FieldB" = IS NULL OR "FieldA" IS NULL AND "FieldB" = :AskB OR "FieldA" IS NULL AND "FieldB" = IS NULL With this code nearly the whole possible rows for criteria in the designer will be blocked. Adding a third field will be impossible in the designer. SELECT * FROM "Table1" WHERE ( "FieldA" = :AskA OR "FieldA" IS NULL) AND ("FieldB" = :AskB OR "FieldB" IS NULL ) This all works well without the GUI. The query doesn't need to be switched to direct SQL. You could input data ... I could confirm the behavior of the GUI. Would be a good idea to let the user decide if he wants to set the columns of the query-GUI in brackets. At this moment the GUI looks first for the rows, adds all content with "AND" and then adds the next row with "OR". This behavior exists since the beginning of LO, so I set the version to "Inherited from OOo"