Long queries could be edited better by setting a return at some points of the query. This queries could be created in SQL-Mode. When I save them in "direct SQL-Mode" an reopen them the returns in the SQL-code are already there. When I dont save them in "direct SQL-Mode" and reopen them the returns in the SQL-code have gone. You could say: Why don't you save them all in "direct-SQL-Mode"? Sorting and setting of filters won't work in this mode. Robert
Thanks for new idea reproduced in 3.5.3 on Fedora 64 bit Carriage returns saved only in "Native SQL" mode (switch to SQL design, then appears "Native SQL" button). All Carriage returns disappears after switch to "Design view".
@ Lionel Greetings What do You thing about this idea?
From what I remember from the top of my head, the whole SQL code is regenerated from its abstract "design view"; not only are carriage returns removed, but the whole code is canonicalised: column names are escaped (within double quotes), order of clauses is fixed, a single fixed choice is made among syntactical variants of SQL, etc. I see two different reasonable ways to handle this: - If one just switches to design view and back to SQL view, not having done ANY CHANGE in the design view, do not regenerate the SQL code, just take the exact string as it is. OR/AND - when generating SQL code from design view abstraction, put newlines at reasonable places; for example between clauses, like that: SELECT "t"."foo", "t"."bar", "t"."baz", "t"."qux" FROM table AS t WHERE condition1 GROUP BY "t"."foo" HAVING condition2 In this solution, there are still details to be filled in, such as whether and where to put newlines within clauses: e.g. mutliple joins in FROM clause, long WHERE clause (put newlines at "high" points of the boolean expression, respecting the structure of the formula), ... E.g. in "WHERE (long1 OR long2) AND long3", but a newline before/after the AND as in: WHERE (long1 OR long2) AND long3 but avoid WHERE (long1 OR long2) AND long3 Also OK: WHERE (long1 OR long2) AND long3
Reproduced using LibO Version 3.6.3.2 (Build ID: 58f22d5) on Windows 7 Home Premium 64-bit @Lionel IMHO it's better to insert more newlines than less to create or save clearly arranged SQL code. If you need regeneration of SQL code, you should insert newlines at the following places: * before each FROM, WHERE, GROUP BY, HAVING, ORDER BY, UNION * after UNION * before each JOIN: ** [INNER] JOIN ** LEFT | RIGHT [OUTER] JOIN ** CROSS JOIN * before each AND in WHERE clause * after each comma in the column list * after a closing bracket in WHERE clause ** but not between two closing brackets like (a AND (b OR c)) OR (d AND e) * before WHEN and ELSE in CASE...END The newline should be indented by spaces: * 0 before SELECT, UNION * 7 within column list * 2 before FROM, JOINs * 2 before WHERE, GROUP BY, HAVING, ORDER BY * 7 within WHERE clause There are more conceivable rules, but IMHO not necessary. Hope this way can be realized in near future. Juergen
Adding self to CC if not already on
Still the same in 4.1.6.2. The entire SQL statement gets reformatted without any line breaks or extra spaces. I never bother with the query design view but always enter SQL code directly,. Since my queries frequently these tend to be somewhat complex, I format them with line breaks and indentation to make them more legible at least while I create them. Therefore I'd strongly favor storing the SQL source as plain text, with all formatting preserved, rather than in some abstraction format, when the last edit to the query was in SQL mode. Anyone who codes is used to that behavior from their IDE or editor of choice, and I'd like to see the same in LO.
*** Bug 122212 has been marked as a duplicate of this bug. ***
Still occurs in 7.5.3.2 on Win10 with loss of all newlines. Also the conversion of identifier delimiting square brackets to grave accents (backticks) is annoying when working with Access databases (*.mdb, *.accdb) through JDBC/UCanAccess.
(In reply to Jeff D. Hanson from comment #8) > > Also the conversion of identifier delimiting square brackets to grave > accents (backticks) is annoying when working with Access databases (*.mdb, > *.accdb) through JDBC/UCanAccess. Pleas don't mix different problems in one bug description. This is asking for an enhancement for returns in SQL-code, not for different delimiter.