[Solved] SQL For obvious dummies

[captkirk]

I currently have a table which contains, among other things, a Field called Income/Expense which contains either "Income" or "Expense" as a value, and another field called Transaction Amount, (currency). I want to identify all records where the value of Income/Expense is "Income" and use this to change the "Transaction Amount" in that record to negative Transaction Amount. eg, a Transaction amount of $123.00 would become -$123.00.
Tried this but just got an error.
CASE WHEN "Account Transactions"."Income/Expense" is"Income" THEN "Account Transactions"."Transaction Amount" IS - "Account Transactions"."Transaction Amount";
Would appreciate any help on this one.

[rudolfo]

The change that you want to apply won't affect all records but only those that have 'Income' in the Expense/Income column. CASE WHEN is rather advanced SQL where you apply something to all records (or a group of records) but what you apply is different based on a certain additional criteria of the record.

If you want to affect only some record you are supposed to use a filter method. In SQL parlance this is a WHERE clause:

Code: Select all

UPDATE "Account Transactions" SET  "Account Transactions"."Transaction Amount" = - "Account Transactions"."Transaction Amount"
  WHERE "Account Transactions"."Income/Expense" = 'Income'

This looks rather terrible because the spaces in the table and column names have brought you directly into quoting hell.
With optimized table and column names and in good old ANSI SQL it is a lot easier to understand. Note that you don't have to fully qualify the fields with table.column_name if you use only one table in your statement. This kind of redundancy with the fully qualified names is for stupid machines (and mostly generated by stupid machines). Dropping the redundancy from the statement makes the logic more obvious:

Code: Select all

UPDATE account_transactions SET amount = - amount WHERE income_or_expense = 'Income'

Column values that have a text or string type value need to be inclused in single quotes. This is mandatory (in all flavours of SQL). While the enclosing of column/table names with double quotes is optional (if you have chosen well behaved names). Of course a slash which would be interpreted as the division operator and a space in the name must be escaped with the enclosing double quotes.

[DACM]

You didn't really describe the error so I'll mention that a Query stored in your Base (.odb) file does not support DML/DDL ( Data Manipulation/Definition Language ) statements that change the database.

So assuming that you're using the SQL console (Tools > SQL...) in Base to issue the SQL,
try using CASE WHEN within an UPDATE statement:

• Code: Select all

  UPDATE "Account Transactions" SET "Transaction Amount" = CASE 
        WHEN "Income/Expense" = 'Expense' AND "Transaction Amount" > 0 THEN "Transaction Amount"*(-1) 
        ELSE "Transaction Amount" 
        END;

If you need to run this UPDATE frequently in the course of your workflow, then it may be helpful to run the SQL from a macro assigned to a Push Button on a Form. Otherwise, there's no easy way to store DML statements such as this in Base:

• Code: Select all

  Sub Change_To_Negative (oEvent As Object) 'Button > Execute > event
  	oForm = oEvent.Source.Model.Parent 'MainForm from Button
  	oStatement = oForm.ActiveConnection.createStatement() 'Create an SQL statement object
  	sSQL = "UPDATE ""Account Transactions"" SET ""Transaction Amount"" = CASE "
          sSQL = sSQL & "WHEN ""Income/Expense"" = 'Expense' AND ""Transaction Amount"" > 0 "
          sSQL = sSQL & "THEN ""Transaction Amount""*(-1) "
          sSQL = sSQL & "ELSE ""Transaction Amount"" "
          sSQL = sSQL & "END"
  	oStatement.executeUpdate( sSQL ) 'Execute the SQL command
  	oForm.reload
  	oForm.last
  End Sub

The following file is a modified example database using a couple of variations of the above macro. The Investments form in the following example includes two Push Buttons: Buy = Negative & Buy = Positive. To reverse-engineer the example, notice that the Push Buttons each have a macro assigned to an Event in their Properties dialog:

[DACM]

...the enclosing of column/table names with double quotes is optional (if you have chosen well behaved names).

True.

As I understand it, the ANSI standard makes a distinction between regular (un-escaped; unquoted) and delimited (escaped; double-quoted) identifiers. Regular identifiers are not case-sensitive and require more restrictive naming conventions (one_word, no keywords, limited special characters, starts with a letter and is followed by any number of ASCII letters or digits). According to the standards, these (regular; unquoted) identifiers must be folded into ALL CAPS upon creation (although at least one prominent engine folds regular identifiers to lowercase). On the other hand, the SQL standards actually require that delimited identifiers be case-sensitive, although not all database-engines enforce this.

HSQLDB simply follows the standards for both quoted and unquoted identifiers (it appears).

Base is another story. We should make a distinction between the Base GUI (Graphical User Interface) and the Base SQL console (Tools > SQL...). The GUI includes the Table Wizards, built-in Table Edit functions, and any 'parsed' Queries. The SQL console is simply a command-line utility that passes SQL directly to the database engine. It's the GUI that enforces delimited identifiers through the use of double-quotes; if you get lazy, the GUI simply adds the necessary quotes before passing the SQL to the engine. I'm sure this was a pragmatic decision since delimited identifiers add naming flexibility (any Unicode character including spaces) and maximize compatibility (no keyword collisions), but admittedly at the expense of tedious double-quoting in the SQL Console and in macros.

But we do have a workaround. If we are diligent to use ALL_CAPS (while adhering to restrictions associated with regular identifiers) when creating a table/column/view name, then we effectively create a regular (unquoted; un-escaped; case-insensitive) name. Thus, double-quotes become optional when using the Base SQL console (Tools > SQL...) as rudolfo prefers (above). So a "well-behaved name" is one created using ALL_CAPS with the underscore character substituted for spaces.

Of course a slash which would be interpreted as the division operator and a space in the name, [so the entire name] must be escaped with...double quotes.

All of this to say that captkirk apparently created case-sensitive names (delimited identifiers) using the Base GUI so double-quotes are necessary. The slashes, spaces, case-sensitivity, and any other Unicode character are fair-game within double-quotes. Unfortunately, delimited identifiers require double-double-quotes within macros, as demonstrated above. This creates some debugging and readability challenges just as rudolfo highlights.

[rudolfo]

I hoped that DACM would add an answer about the options to work with HSQLDB without the need for quotes. I was actually thinking about adding a note about this in paranthesis when I wrote my last post. But then I decided that it wouldn't help anybody if I didn't explain the details and that I am too lazy and not skilled enough to add this further explanation. And after all I only wanted to make the point that SQL is close to the human language if you are lucky to learn it with a decent dialect like Oracle or MySQL or other engines that don't force you into a long search for the SELECT, UPDATE or WHERE.
Anyway, I am glad that DACM added the comment to make this complete and I'm even more glad about how this thread has evolved. That's what I like about this forum ... it lets the bits and pieces come together.

[DACM]

Well I just hope that I've displayed the proper humility in the presence of one of my mentors in this forum: rudolfo. Even my initial participation in this thread amounted to a cross-post as I was unaware of rudolfo's reply when I submitted a reply -- as you can tell by the close timing .

But I'd like to seize the opportunity to glean from rudolfo's depth of experience on this side-topic (identifiers)...

...the options to work with [Base+HSQLDB] without the need for quotes.

• Can we assume that you're referring the options necessary when using the Base GUI (wizards, table creator, etc.) as the DDL manager to create/name tables, columns, etc? That was my intended target for the ALL_CAPS workaround, although I didn't make that distinction very clear. Despite the options and included AWT database manager bundled within HSQLDB (hsqldb.jar) -- I presume that the majority of users in this forum use the Base GUI as the DDL manager for HSQLDB during schema development (Villeroy notwithstanding). I confess that I use the GUI more often than not, unless production data is somehow at stake or the GUI feature is missing or broken. On the other hand, I presume that most Oracle and MySQL databases in use by forum participants are created utilizing an alternate DDL/DML manager, even if the database is eventually paired with Base.
  
  This is significant, because the Base GUI adds double-quotes when passing names thereby creating 'delimited identifiers' and enforcing case-sensitivity in the backend. Other database managers do not add quotes (by default?), so the database engine folds lowercase names (as needed) into UPPERCASE in accordance with the SQL Standards. So Base creates case-sensitive names while other managers create case-insensitive names. From that point on, it seems unfair to blame the database engine for following the SQL Standards with respect to quoting requirements. If the Base GUI were used to create names in Oracle or MySQL, then the names would forever require quoting/escaping -- unless utilizing the ALL_CAPS workaround at creation -- assuming these engines comply with the SQL Standards (which is a stretch for MySQL). As far as I can tell, HSQLDB, like Oracle and to some extent MySQL, simply represent faithful implementations of the SQL standards.

...I only wanted to make the point that SQL is close to the human language if you are lucky to learn it with a decent dialect like Oracle or MySQL or other engines that don't force you into a long search for the SELECT, UPDATE or WHERE.

• I presume "long search" here refers to the seriously sub-standard documentation for HSQLDB 1.8...? Of course, I would agree (but I need to be careful because just the accusation is akin to volunteering to re-work said documentation in a project like HSQLDB ). I can only say that I've never encountered better documentation than cousin H2 provides (neat, accessible, reflects SQL standards). And since HSQLDB strives for those same SQL standards (and has the same initial author), I simply use H2's documentation as a matter of course -- especially for hand-typed DDL/DML statements.
  
  Otherwise, I don't perceive a distinction between learning the SQL dialect of HSQLDB, Oracle or MySQL. This could be a matter of my relative inexperience. In fact, I've tended to promote H2, and by extension HSQLDB, as perhaps the best platform for learning highly-standardized SQL.