Wednesday, October 10, 2018

Unfortunately, OpenOffice Base Really Does Not Implement SQL Correctly

Every source I can find shows this:
SELECT COUNT( * ) as "Number of Rows"FROM orders WHERE ord_amount>1500;
Note that the word TABLE is not there.  OpenOffice Base complains about the absence of TABLE and says syntax error with the word TABLE in there.  The query wizard is non-functional; conditions do not generate WHERE statements.  Some of you suggested MySQL, but I cannot find a download that isn't an upgrade only version.

I will try the LibreOffice variant next.

7 comments:

  1. Clayton,

    go here https://dev.mysql.com/downloads/windows/installer/8.0.html and scroll down to the "generally available (GA) Releases" section, and click one of the two links.

    The first one, at 15.9MB, is just the installer. It will ask you what components you want, and then download just them. The other one, at 273.4M, has everything.

    ReplyDelete
  2. Clayton:

    Do you have a linux computer available? Installing mysql is effortless in linux.

    Common 'real' database engines available are mariadb (mysql was acquired by Oracle and the original developers forked it into mariadb). You might also look at postgresql, as that is another very capable open source database engine.

    What is your data source? If it is a spreadsheet or the like, I can probably come up with a database design and insert statements for you (I work with SQL in my day job).

    Feel free to email me at the follow-up email address if I can give you a hand on that.

    Wayne

    ReplyDelete
  3. Wayne: I have a Debian Linux variant running in VMWare Player. Sharing data with my Windows file system is not easy, for reasons that elude me. I successfully imported the spreadsheet into OpenOffice Base. I have discovered some fields that should be INT are TEXT. In OpenOffice Base, I was able to use ALTER TABLE... ALTER COLUMN to change field types. But OO Base is severely broken. LibreOffice Base can import OO Base database, but in LO Base:

    Alter table "MassMurder" alter column "dead victim count" INT

    gives

    1: Wrong data type: java.lang.NumberFormatException: For input string: "?" in statement [alter table "MassMurder" alter column "dead victim count" INT]

    ReplyDelete
  4. Can anyone tell me if the SELECT statement above is correct? If it is, I will abandon OpenOffice and LibreOffice. OO is clearly not running real SQL. LO seems broken the same way.

    ReplyDelete
  5. Wayne: I did SQL for a living from 2008-2014. I am suspecting that OO and LO have implemented a defective or incomplete SQL. If so, the only choice is MySQL. But I want to make sure that I am giving valid SQL commands before abandoning LibreOffice.

    ReplyDelete
  6. SELECT COUNT( * ) as "Number of Rows"FROM orders WHERE ord_amount>1500;

    Should be:

    SELECT COUNT( * ) as "Number of Rows" FROM orders WHERE ord_amount>1500;

    (space between the " and the F in FROM.

    This assumes orders is the name of the table. I'd also put spaces around the >.

    ReplyDelete
  7. Clayton:

    The only change I would suggest is to replace "Number of Rows" with NumberOfRows,avoiding the need to escape it. Escaping column aliases can get annoying.

    Honestly, I think you would have an easier time if you change the database architecture to be more like the following:

    CREATE TABLE mass_murder (
    id INT PRIMARY KEY,
    year INT NOT NULL,
    month INT NOT NULL,
    dead_victim_count INT NOT NULL DEFAULT 0,
    injured_victim_count INT NOT NULL DEFAULT 0,
    category VARCHAR(64),
    cause VARCHAR(64),
    weapon VARCHAR(64)
    );

    You can then update your query to be more like the following:


    SELECT
    10*ROUND(mm.year * 10,0) AS decade,
    COUNT(mm.id) AS total_dead,
    SUM(mm.undead_victim_count) AS zombie_count
    FROM
    mass_murders mm
    GROUP BY
    10*ROUND(mm.year * 10,0)
    ORDER BY
    10*ROUND(mm.year * 10,0) ASC;

    And for the detail rows:

    SELECT
    10*ROUND(mm.year * 10,0) AS decade,
    mm.weapon AS weapon,
    COUNT(mm.id) AS total_dead,
    SUM(mm.undead_victim_count) AS zombie_count
    FROM
    mass_murders mm
    GROUP BY
    10*ROUND(mm.year * 10,0),
    mm.weapon
    ORDER BY
    10*ROUND(mm.year * 10,0) ASC,
    mm.weapon ASC;

    If you need a hand migrating a bunch of columnar data to the above format, please let me know. I should be able to parse a CSV using either perl or regexes.

    You mention that MySQL is the only other choice. I would honestly look at MariaDB instead of MySQL. I personally prefer postgresql, but both are decent engines. All three are free and available for Windows or Linux.

    One point to be aware of is that by default MySQL and MariaDB use backticks (`) to enclose table and column names and postgresql follows ANSI SQL and uses double quotes (").

    Wayne

    ReplyDelete