Update SQL does nothing using "Run SQL directly" button

users
#1

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

#2

Base will only run SELECT statements that way. If you want to use other
than SELECT the only way IIRC is to use Tools-SQL which opens "Execute SQL
statement" but can only handle typed stuff, not stored queries. It is
useful for one-off tasks such as merging two tables into one or doing a
field update as you describe.

#3

And, as a "stylistic" suggestion, don't use a separate field for
transaction year, calculate it from transaction date. The reason is
that you've created a new classs of issue for yourself by doing this,
keeping two fields in step.

In general it's best to store basic data and calculate subsidiary
data from it, don't store subsidiary data.

Regards
Mark Stanton
One small step for mankind...

#4

And, as a "stylistic" suggestion, don't use a separate field for
transaction year, calculate it from transaction date. The reason is
that you've created a new classs of issue for yourself by doing this,
keeping two fields in step.

In general it's best to store basic data and calculate subsidiary
data from it, don't store subsidiary data.

Regards
Mark Stanton
One small step for mankind...

The function is Year(name) = year. For best results store the date in
yyyy-mm-dd format,

Typical use is

Select column list

From table list

Where Year(column) = yyyy AND month = mm

There are other date/time functions available.

#5

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

#6

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!

#7

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

#8

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.

#9

Hi Jay,

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

#10

Hi Jay,

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
FROM tables
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.

#11

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.

#12

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:Edit>"Run 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.