[libreoffice-users] Re: Update SQL does nothing using Run SQL directly button
Am 04.10.2012 01:27, David S. Crampton wrote: Register1a is a table with field Trans-Date of type TIMESTAMP. Field Trans-Date-Year is either TIMESTAMP or INTERGER. I have tried with both. Update Register1a Set Register1a.Trans-Date-Year = YEAR(Register1a.Trans-Date); Looks like is should be a very simple SQL statement. It does absolutely nothing. The statement won't run with the F5 key (the run button). It gives The given command is not a SELECT statement. I'm trying to apply SQL learned elsewhere, mostly in MicroBloat Access, and I'm getting nowhere. Help will be appreciated. David Hello, This is fundamental. It is a shame that this mailing list is unable to answer this simple question properly and in depth after all the lengthy topics on Base and Base documentation. menu:EditRun SQL directly (or the SQL toggle button on the tool bar) is the equivalent of the pass-through query in MS Access. The normal operation mode is a parsed query where Base handles the query string. Whenever you want to use backend specific functions (e.g. MySQL GROUP_CONCAT), the specific SQL syntax of the backend or one of the many things that are not (properly) implemented in Base, you can mark the query as direct SQL. Base will ignore the query string and pass it over to the underlying database engine waiting for a record set or some error message in return. A frequent issue with HSQLDB is the UNION statement which requires direct mode: SELECT Date, Text, Number FROM Table A UNION ALL SELECT Date, Text, Number FROM Table B Direct SQL is not the solution to all problems. -- The returned record set is always read-only. -- Parameter queries and nested queries are Base features. No backend can deal with that. -- Pairs of forms and subforms require two parsed queries, otherwise the subform ignores the binding to its parent. Hope this helps, A.S. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Base list, was: Fw: [libreoffice-users] Re: Update SQL does nothing using Run SQL directly button
Hi :) All the threads about Base are buried in the archives: scattered and hidden, incoherent amongst hundreds of threads about things that are nothing to do with Base. If there was a Base List then some of those threads might have been referenced on it. There isn't. So any thread about Base only has whoever happens to be on that particular list and anything about base gets swallowed up amongst all the threads about other issues. Regards from Tom :) --- On Mon, 8/10/12, Andreas Säger ville...@t-online.de wrote: From: Andreas Säger ville...@t-online.de Subject: [libreoffice-users] Re: Update SQL does nothing using Run SQL directly button To: users@global.libreoffice.org Date: Monday, 8 October, 2012, 7:49 Am 04.10.2012 01:27, David S. Crampton wrote: Register1a is a table with field Trans-Date of type TIMESTAMP. Field Trans-Date-Year is either TIMESTAMP or INTERGER. I have tried with both. Update Register1a Set Register1a.Trans-Date-Year = YEAR(Register1a.Trans-Date); Looks like is should be a very simple SQL statement. It does absolutely nothing. The statement won't run with the F5 key (the run button). It gives The given command is not a SELECT statement. I'm trying to apply SQL learned elsewhere, mostly in MicroBloat Access, and I'm getting nowhere. Help will be appreciated. David Hello, This is fundamental. It is a shame that this mailing list is unable to answer this simple question properly and in depth after all the lengthy topics on Base and Base documentation. menu:EditRun SQL directly (or the SQL toggle button on the tool bar) is the equivalent of the pass-through query in MS Access. The normal operation mode is a parsed query where Base handles the query string. Whenever you want to use backend specific functions (e.g. MySQL GROUP_CONCAT), the specific SQL syntax of the backend or one of the many things that are not (properly) implemented in Base, you can mark the query as direct SQL. Base will ignore the query string and pass it over to the underlying database engine waiting for a record set or some error message in return. A frequent issue with HSQLDB is the UNION statement which requires direct mode: SELECT Date, Text, Number FROM Table A UNION ALL SELECT Date, Text, Number FROM Table B Direct SQL is not the solution to all problems. -- The returned record set is always read-only. -- Parameter queries and nested queries are Base features. No backend can deal with that. -- Pairs of forms and subforms require two parsed queries, otherwise the subform ignores the binding to its parent. Hope this helps, A.S. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Update SQL does nothing using Run SQL directly button
Hi Jay, On 6 October 2012 14:55, Jay Lozier jsloz...@gmail.com wrote: This actually standard practice for writing queries. Another common practice is to capitalize SQL key words, which you appear to also do. I was aware that formatting a query was good practice - not so sure that it is standard practice. If it is, then the standard, in my experience, seems to have lots of variations! I've seen some queries that were pretty hard to read. Because the compiler doesn't care about formatting, it seems that many people come up with their own standard (or have it enforced by their employer). Be that as it may, the main point I was making in my post was that with the SQL Button off, any formatting is stripped out. The query is apparently handled by the Query Wizard which doesn't seem to know anything about a formatting standard. It would be good if this could be added to the wizard some time. I think code to do that would be quite tricky to write, which may explain why it hasn't been done to date. Thanks for your comment. Noel -- Noel Lodge lodg...@gmail.com -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Update SQL does nothing using Run SQL directly button
On 10/07/2012 07:28 AM, Marion Noel Lodge wrote: Hi Jay, On 6 October 2012 14:55, Jay Lozier jsloz...@gmail.com wrote: This actually standard practice for writing queries. Another common practice is to capitalize SQL key words, which you appear to also do. I was aware that formatting a query was good practice - not so sure that it is standard practice. If it is, then the standard, in my experience, seems to have lots of variations! I've seen some queries that were pretty hard to read. Because the compiler doesn't care about formatting, it seems that many people come up with their own standard (or have it enforced by their employer). I tend to follow the formatting practices I see in most texts. SELECT columns FROMtables WHERE conditions SELECT This seems to be reasonably readable in many situations. What I have seen to difficult to follow is a very complex WHERE clause. Be that as it may, the main point I was making in my post was that with the SQL Button off, any formatting is stripped out. The query is apparently handled by the Query Wizard which doesn't seem to know anything about a formatting standard. It would be good if this could be added to the wizard some time. I think code to do that would be quite tricky to write, which may explain why it hasn't been done to date. Thanks for your comment. Noel -- Noel Lodge lodg...@gmail.com -- Jay Lozier jsloz...@gmail.com -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Update SQL does nothing using Run SQL directly button
Jay Noel, One other effect of the SQL button in Query Design appears to be that it disables the GUI style design view. If one wants to use both the GUI table (to get started with the basics) and then fine tune with written SQL, you have to sacrifice the formatting of the SQL phrases. If you return to the GUI table, and then to the SQL, the SQL phrases will be unformatted: just a long line. - -- David S. Crampton -- View this message in context: http://nabble.documentfoundation.org/Update-SQL-does-nothing-using-Run-SQL-directly-button-tp4010902p4011942.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Update SQL does nothing using Run SQL directly button
John and the others who responded, It took me a while to realize that the useful SQL menu is available only on the main / opening window of the db. The SQL button in the Query Design view still appears to do nothing. I'm finally getting some traction. I acknowledge and appreciate the stylistic suggestions. This year and month data is very unlikely to change once the transaction record is accurately entered. Thank you, all, David - -- David S. Crampton -- View this message in context: http://nabble.documentfoundation.org/Update-SQL-does-nothing-using-Run-SQL-directly-button-tp4010902p4011374.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Update SQL does nothing using Run SQL directly button
That button, according to the help file, toggles whether the SQL is passed directly to the database engine or handled by Base first. I'm not quite sure what that means in effect! On 5 October 2012 18:12, David S. Crampton david_cramp...@ie2b.com wrote: John and the others who responded, It took me a while to realize that the useful SQL menu is available only on the main / opening window of the db. The SQL button in the Query Design view still appears to do nothing. I'm finally getting some traction. I acknowledge and appreciate the stylistic suggestions. This year and month data is very unlikely to change once the transaction record is accurately entered. Thank you, all, David - -- David S. Crampton -- View this message in context: http://nabble.documentfoundation.org/Update-SQL-does-nothing-using-Run-SQL-directly-button-tp4010902p4011374.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Update SQL does nothing using Run SQL directly button
I discovered one useful feature of the SQL Button. I like to format my SQL queries by putting SELECT, FROM, WHERE etc. each on a separate line, plus I use tabs to line up the various table names, again on their own line. I find this makes complex queries much more readable. If the SQL Button is on, then this formatting is saved when the query is saved. If the button is off, then next time the query is opened in edit mode, the code is all in one long line! I used to find this quite annoying. Noel -- Noel Lodge lodg...@gmail.com On 6 October 2012 03:23, John Clegg john.cl...@nailsea.net wrote: That button, according to the help file, toggles whether the SQL is passed directly to the database engine or handled by Base first. I'm not quite sure what that means in effect! On 5 October 2012 18:12, David S. Crampton david_cramp...@ie2b.com wrote: John and the others who responded, It took me a while to realize that the useful SQL menu is available only on the main / opening window of the db. The SQL button in the Query Design view still appears to do nothing. I'm finally getting some traction. I acknowledge and appreciate the stylistic suggestions. This year and month data is very unlikely to change once the transaction record is accurately entered. Thank you, all, David - -- David S. Crampton -- View this message in context: http://nabble.documentfoundation.org/Update-SQL-does-nothing-using-Run-SQL-directly-button-tp4010902p4011374.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Update SQL does nothing using Run SQL directly button
On 10/05/2012 10:13 PM, Marion Noel Lodge wrote: I discovered one useful feature of the SQL Button. I like to format my SQL queries by putting SELECT, FROM, WHERE etc. each on a separate line, plus I use tabs to line up the various table names, again on their own line. I find this makes complex queries much more readable. This actually standard practice for writing queries. Another common practice is to capitalize SQL key words, which you appear to also do. If the SQL Button is on, then this formatting is saved when the query is saved. If the button is off, then next time the query is opened in edit mode, the code is all in one long line! I used to find this quite annoying. Noel -- Noel Lodge lodg...@gmail.com On 6 October 2012 03:23, John Clegg john.cl...@nailsea.net wrote: That button, according to the help file, toggles whether the SQL is passed directly to the database engine or handled by Base first. I'm not quite sure what that means in effect! On 5 October 2012 18:12, David S. Crampton david_cramp...@ie2b.com wrote: John and the others who responded, It took me a while to realize that the useful SQL menu is available only on the main / opening window of the db. The SQL button in the Query Design view still appears to do nothing. I'm finally getting some traction. I acknowledge and appreciate the stylistic suggestions. This year and month data is very unlikely to change once the transaction record is accurately entered. Thank you, all, David - -- David S. Crampton -- View this message in context: http://nabble.documentfoundation.org/Update-SQL-does-nothing-using-Run-SQL-directly-button-tp4010902p4011374.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- Jay Lozier jsloz...@gmail.com -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
