Subscribe to this thread
Home - Cutting Edge / All posts - Arithmetic operations from Fehily
dyalsjas59 post(s)
#06-Feb-18 02:21

Still working through Fehily 2016,

I can't seem to find how this query (text listing 0507) may work in Manifold. The text listings from books.map and books2.map don't seem to work. I've been able to run these in MS Access and SQL Server, and have been able to get the similar singular expressions (text listings 0501 and 0502) to run.

Here's a copy of the text listing from Fehily:

SELECT

2 + 3 * 4 AS "2+3*4",

(2 + 3) * 4 AS "(2+3)*4",

6 / 2 * 3 AS "6/2*3",

6 / (2 * 3) AS "6/(2*3)";

Here's the result text listing from Fehily:

2+3*4 (2+3)*4 6/2*3 6/(2*3)

----- ------- ----- -------

14 20 9 1

Dimitri


4,596 post(s)
#06-Feb-18 05:56

Well, one of the core lessons from Fehily is that different systems work differently, so you have to learn about the nuances.

In Manifold you can use the query builder to learn some of the basic syntax requirements. For example, the SELECT template from the query builder is...

SELECT <field>/SPLIT <query>, ... FROM <table>

A SELECT cannot live on its own without that FROM and a table. The simple form

SELECT A as B;

Is wrong. Ain't no FROM there, and no table.

SELECT A as B FROM MyTable;

is OK.

Likewise, the use of AS outside of a clause that produces a table doesn't work. If you create a table, sure, alias fields.

If you want to just evaluate an expression use ?

? 2 + 3 * 4

See the Command Window topic.

Ah, last but not least, despite the intellectual fun of playing tricky games involving the creation of incomprehensible column / alias names, you might have to escape special characters when constructing the string for the column name / alias. It is far wiser to avoid playing tricky games, since the objective is to produce queries that will not fool other people or you five minutes after they have been written. :-)

dyalsjas59 post(s)
#06-Feb-18 23:26

My initial effort in understanding how to execute the text listing referenced in my posting above led me to the Command Window topic. I was able to evaluate each of the four expressions listed (without the associated "AS" statement); following the information provided in the Manifold help, e.g. ? 2 + 3 * 4

My question to the forum was to assist in discovering if I had missed something from the another part of the Manifold help documentation; your posting helps me realize I haven't.

The capability of evaluating multiple expressions, with defined labels on output, while available in Access and SQL Server is not replicated in the Manifold Command Window. No problem, I'll update the text listing in my books.map file and move forward.

If there is interest, when I've updated all of the Fehily text listings in books.map and books2.map to work with Manifold 9 (to the extent possible) I'll be happy to share.

In that the Fehily books are the Manifold recommend learning guides for SQL, Manifold project files that show the examples working in Manifold may be useful to others.

Or perhaps others will prefer discovery learning by verifying and editing the queries from the Microsoft Access files provided on Fehily.com or the books.map and books2.map files available for download from Manifold.net.

tjhb

7,778 post(s)
#06-Feb-18 23:34

The capability of evaluating multiple expressions, with defined labels on output, while available in Access and SQL Server is not replicated in the Manifold Command Window. No problem, I'll update the text listing in my books.map file and move forward.

No, that is completely incorrect. You have drawn a false inference in this case.

(I will post a better reply below.)

tjhb

7,778 post(s)
#06-Feb-18 23:54

The issue here is this.

Chris Fehily is targeting ISO SQL and therefore writes "standard" SQL, adding call-out notes for exceptions, for the DBMS systems he addresses.

"Standard" SQL doesn't exist in the wild. Which is no criticism of Fehily. He is writing one book, and needs to achieve a balance.

The issue here is that many (most?) flavours of SQL require a SELECT clause to draw upon a FROM clause. (As Dimitri says.) I personally think it was a mistake for Fehily to use any example that did not draw on a FROM clause. But he probably thought about it quite a bit.

As Fehily says (p.119 in the same print edition):

Oracle requires a FROM clause in a SELECT statement...

DB2 requires a FROM clause in a SELECT statement...

Manifold SQL8 and SQL9 are in the same group. No SELECT without FROM (as Dimitri has said). That's completely normal.

To get a similar result in Manifold 8 or 9, consider a VALUES statement. (Again completely standard, but AFAIK Fehily does not cover it.)

tjhb

7,778 post(s)
#07-Feb-18 00:01

Here is how you would translate the query you posted into Manifold 9 SQL.

VALUES (

    2 + 3 * 4, 

    (2 + 3) * 4, 

    6 / 2 * 3, 

    6 / (2 * 3)

    )

AS (

    [2 + 3 * 4],

    [(2 + 3) * 4]

    [6 / 2 * 3],

    [6 / (2 * 3)]

    )

;

Manifold 8 SQL is nearly the same, but you replace AS with NAMES.

dyalsjas59 post(s)
#07-Feb-18 00:34

Thank you very much. I love learning. I'll look up VALUES in the Manifold help file.

BTW, Wow!! The results are like those offered the the Fehily text.

Now I'll need to find other references to study as well.

Dimitri


4,596 post(s)
#07-Feb-18 07:07

Good example, Tim. It's very educational to just copy your VALUES example and paste into a Command Window, hit the ! button and see the result. I so rarely ever write a query that does not begin with SELECT it is easy to forget SELECT is not the only clause in town.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.