Just curious

John McKown

Just curious
I realize this is not really a DB2 question, per se. But I've got a "why"
type question. The answer is most likely "because" <grin>. Why must the
clauses in a SELECT (or UPDATE) command be in any specific order? Since the
phrases are "key words", why must I always remember to specify FROM before
WHERE, and WHERE before GROUP BY, and GROUP BY before HAVING, and HAVING
before ORDER BY. Would it really be that much harder to parse the SQL
statement if I specified these clauses in any order? I guess that if I did
SQL a lot, I'd just remember the correct order.

Again, just curious.

----------------------------------------------------------------------
John McKown
Applications and Solutions Team
Healthaxis



Georg Peter

AW: Just curious
(in response to John McKown)
John,

it works as designed ;-))

With kind regards - mit freundlichen Grüssen,
Georg H. Peter
----------------------------------------------------------------------
By the way: Unless you are going to retire in 5-10 years you better learn
Unicode.


-----Ursprüngliche Nachricht-----
Von: McKown, John [mailto:[login to unmask email]
Gesendet am: Dienstag, 8. Januar 2002 15:57
An: [login to unmask email]
Betreff: Just curious

I realize this is not really a DB2 question, per se. But I've got a "why"
type question. The answer is most likely "because" <grin>. Why must the
clauses in a SELECT (or UPDATE) command be in any specific order? Since the
phrases are "key words", why must I always remember to specify FROM before
WHERE, and WHERE before GROUP BY, and GROUP BY before HAVING, and HAVING
before ORDER BY. Would it really be that much harder to parse the SQL
statement if I specified these clauses in any order? I guess that if I did
SQL a lot, I'd just remember the correct order.

Again, just curious.

----------------------------------------------------------------------
John McKown
Applications and Solutions Team
Healthaxis


visit the

.



[login to unmask email]

Re: Just curious
(in response to Georg Peter)
John, my first guess is that you've never been a programmer that had to
parse data strings, my second is that if you could write it anyway you
wanted to it could look like gibberish no one could understand...

George




"McKown, John" <[login to unmask email]>@RYCI.COM> on 01/08/2002 09:57:19 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Just curious


realize this is not really a DB2 question, per se. But I've got a "why"
type question. The answer is most likely "because" <grin>. Why must the
clauses in a SELECT (or UPDATE) command be in any specific order? Since the
phrases are "key words", why must I always remember to specify FROM before
WHERE, and WHERE before GROUP BY, and GROUP BY before HAVING, and HAVING
before ORDER BY. Would it really be that much harder to parse the SQL
statement if I specified these clauses in any order? I guess that if I did
SQL a lot, I'd just remember the correct order.

Again, just curious.

----------------------------------------------------------------------
John McKown
Applications and Solutions Team
Healthaxis








Peter J Krawetzky

Re: Just curious
(in response to truman.g.brown@VERIZON.COM)
ANSI SQL is the standard used throught the industry for DB2 (there might be
some variation depending on the operating system). This provides a
consistent way to code SQL statements. Can you image the time it would take
to learn the code if you move from one company to another and find that the
SQL code is not in a specific sequence? It's really no different than
coding COBOL or assembler on OS/390.

Peter J. Krawetzky, DBA
IBM Certified Solutions Expert
DB2 UDB V7.1 Database Administration For Unix, Windows and OS/2


-----Original Message-----
From: McKown, John [mailto:[login to unmask email]
Sent: Tuesday, January 08, 2002 9:57 AM
To: [login to unmask email]
Subject: Just curious


I realize this is not really a DB2 question, per se. But I've got a "why"
type question. The answer is most likely "because" <grin>. Why must the
clauses in a SELECT (or UPDATE) command be in any specific order? Since the
phrases are "key words", why must I always remember to specify FROM before
WHERE, and WHERE before GROUP BY, and GROUP BY before HAVING, and HAVING
before ORDER BY. Would it really be that much harder to parse the SQL
statement if I specified these clauses in any order? I guess that if I did
SQL a lot, I'd just remember the correct order.

Again, just curious.

----------------------------------------------------------------------
John McKown
Applications and Solutions Team
Healthaxis








Robert Jans

Re: Just curious
(in response to Peter J Krawetzky)
John, I'm sure you'll get bunches of replies to this :)

I would hazard (not being an SQL guru) that the 'key words' are required in
a specific order so the parse can determine what table a columns belongs to
and what really the grouping should be since the columns could be in more
that one table...........


Robert Jans
Albertson's Inc
<Standard Disclaimers Apply>

-----Original Message-----
From: McKown, John [mailto:[login to unmask email]
Sent: Tuesday, January 08, 2002 7:57 AM
To: [login to unmask email]
Subject: Just curious


I realize this is not really a DB2 question, per se. But I've got a "why"
type question. The answer is most likely "because" <grin>. Why must the
clauses in a SELECT (or UPDATE) command be in any specific order? Since the
phrases are "key words", why must I always remember to specify FROM before
WHERE, and WHERE before GROUP BY, and GROUP BY before HAVING, and HAVING
before ORDER BY. Would it really be that much harder to parse the SQL
statement if I specified these clauses in any order? I guess that if I did
SQL a lot, I'd just remember the correct order.

Again, just curious.

----------------------------------------------------------------------
John McKown
Applications and Solutions Team
Healthaxis








John McKown

Re: Just curious
(in response to Robert Jans)
> -----Original Message-----
> From: Peter, Georg [SMTP:[login to unmask email]
> Sent: Tuesday, January 08, 2002 9:19 AM
> To: [login to unmask email]
> Subject: AW: Just curious
>
> John,
>
> it works as designed ;-))
>
> With kind regards - mit freundlichen Grüssen,
> Georg H. Peter
> ----------------------------------------------------------------------
> By the way: Unless you are going to retire in 5-10 years you better learn
> Unicode.
>
>
All of Unicode? That will take me 5-10 years! But I'm already familiar with
the ASCII subset <grin>.

I guess that I was right about the reason for the clauses being in a
specific order. It is "because that's what the standard says." Well, given
the weird rules here at work, I can accept that <grin>.


----------------------------------------------------------------------
John McKown
Applications and Solutions Team
Healthaxis



Bill (Pittsburgh) Johnson

Re: Just curious
(in response to John McKown)
Maybe we could change the name to QL from SQL since under John's rules it
would no longer be structured!

Bill Johnson
Russell Mellon

-----Original Message-----
From: McKown, John [mailto:[login to unmask email]
Sent: Tuesday, January 08, 2002 9:57 AM
To: [login to unmask email]
Subject: Just curious


I realize this is not really a DB2 question, per se. But I've got a "why"
type question. The answer is most likely "because" <grin>. Why must the
clauses in a SELECT (or UPDATE) command be in any specific order? Since the
phrases are "key words", why must I always remember to specify FROM before
WHERE, and WHERE before GROUP BY, and GROUP BY before HAVING, and HAVING
before ORDER BY. Would it really be that much harder to parse the SQL
statement if I specified these clauses in any order? I guess that if I did
SQL a lot, I'd just remember the correct order.

Again, just curious.

----------------------------------------------------------------------
John McKown
Applications and Solutions Team
Healthaxis








Todd Burch

Re: Just curious
(in response to Bill (Pittsburgh) Johnson)
I'm way why it's exactly not done that sure.

Todd.

(Or, using the ANSI standard construction, "I'm not exactly sure why it's done that way")


-----Original Message-----
From: [login to unmask email]
Sent: Tuesday, January 08, 2002 9:16 AM
To: [login to unmask email]
Subject: Just curious


-------------------------------------------------------------------------- --
I realize this is not really a DB2 question, per se. But I've got a "why"
type question. The answer is most likely "because" <grin>. Why must the
clauses in a SELECT (or UPDATE) command be in any specific order? Since
the
phrases are "key words", why must I always remember to specify FROM before
WHERE, and WHERE before GROUP BY, and GROUP BY before HAVING, and HAVING
before ORDER BY. Would it really be that much harder to parse the SQL
statement if I specified these clauses in any order? I guess that if I did
SQL a lot, I'd just remember the correct order.

Again, just curious.

----------------------------------------------------------------------
John McKown
Applications and Solutions Team
Healthaxis



the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
can

Todd Burch
CDB Software, Inc.
Tel: 800-627-6561 or 281-920-3305
Internet Address: [login to unmask email]
Home Page: http://www.cdbsoftware.com



[login to unmask email]

Re: Just curious
(in response to Todd Burch )
Here's my 2 cents worth.

The answer is a little more than just 'because the standard says so'.

As one other post I've seen mentions, it is much easier both logically (to
think of all the possible matches) and in terms of number of lines of code
(and therefore ultimately execution speed wise) to try and parse something
that follows a definite syntax. The fixed syntax limits the number of
things you have to check at each step, making it simpler to check all the
appropriate rules.

If there was no specific order, then (very roughly) the logic for syntax
checking would be something like this

Read a word
if it is a keyword
then read next word
else
If a non keyword appropriate for current keyword
then read next word
else
error
end if
current keyword has passed syntax check, set a flag indicating that this
keyword is present and 'ok'

After all words in SQL pass syntax check
for each keyword flagged as present,
are all required subordinate keywords present
are there any mutually exclusive keywords present
end


With an explict syntax the logic is much simpler and might start something
like this
If the first word is SELECT
continue reading words until you find WHERE
if no WHERE found
oops error, missing appostrophe etc
else
parse words between SELECT and WHERE looking for valid functions
end
ELSE
oops, error message
end if






"McKown, John" <[login to unmask email]>@RYCI.COM> on 2002/01/08 09:52:12 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: Just curious


> -----Original Message-----
> From: Peter, Georg [SMTP:[login to unmask email]
> Sent: Tuesday, January 08, 2002 9:19 AM
> To: [login to unmask email]
> Subject: AW: Just curious
>
> John,
>
> it works as designed ;-))
>
> With kind regards - mit freundlichen Grüssen,
> Georg H. Peter
> ----------------------------------------------------------------------
> By the way: Unless you are going to retire in 5-10 years you better learn
> Unicode.
>
>
All of Unicode? That will take me 5-10 years! But I'm already familiar with
the ASCII subset <grin>.

I guess that I was right about the reason for the clauses being in a
specific order. It is "because that's what the standard says." Well, given
the weird rules here at work, I can accept that <grin>.


----------------------------------------------------------------------
John McKown
Applications and Solutions Team
Healthaxis


visit






Morrill John M

Re: Just curious
(in response to Rohn.Solecki@MTS.MB.CA)
Greetings John!

To understand the reason why, you have to consider two facts.

First, as you add options to a syntax, the parser grows larger, more complex
and consumes more memory and CPU time. If you add the option of allowing
keywords to appear in any order, you have to add a significant amount of
code to your parser to handle the option and thus your parser will consume
more resources.

Second, SQL was first written in the 1970's when the biggest mainframe have
16mg of memory. (How many people remember Bill Gates say 640K memory was all
a personal computer would ever need?).

When I first use DB2 in 1984, the COBOL programs I wrote with embedded SQL
had to be run in a separate batch queue from the "normal" COBOL compiles
because the binding required "a significantly larger amount of resources".

Even today parsing and binding (access path calculation) is a significant
concern because most of today's web applications use dynamic SQL instead of
static SQL. This is due to the fact interpreted script languages are used
extensively in web applications. As a result, ever time a web page makes a
query to a database, the SQL statement has to be parsed and bounded. If this
web page has a high number of hits per hour, the amount of resources
consumed for parsing and binding become significant.

I know at this point a lot of people will argue that dynamic SQL is not the
best practice, and I agree. But the reality is that is what is being done in
a lot of web development. Therefore, in order to maintain performance the
database vendors have to optimize the parsers however they can. An since
there is a large demand for dynamic SQL in applications and little or no
demand for flexibility in keyword order, that is why parsers are still
written to expect SQL keywords to be in a certain order.

I hope this helps.

Cheers!

John


-----Original Message-----
From: McKown, John [mailto:[login to unmask email]
Sent: Tuesday, January 08, 2002 7:57 AM
To: [login to unmask email]
Subject: Just curious


I realize this is not really a DB2 question, per se. But I've got a "why"
type question. The answer is most likely "because" <grin>. Why must the
clauses in a SELECT (or UPDATE) command be in any specific order? Since the
phrases are "key words", why must I always remember to specify FROM before
WHERE, and WHERE before GROUP BY, and GROUP BY before HAVING, and HAVING
before ORDER BY. Would it really be that much harder to parse the SQL
statement if I specified these clauses in any order? I guess that if I did
SQL a lot, I'd just remember the correct order.

Again, just curious.

----------------------------------------------------------------------
John McKown
Applications and Solutions Team
Healthaxis








Roger Miller

Re: AW: Just curious
(in response to James Campbell)
The short answer is that parsing is not nearly that simple. We need to
comply with the SQL standards, which are now about 1700 pages long, and
still growing. The keywords are not adequate in themselves and position in
the statement does often have a different meaning.

Roger Miller, DB2 for z/OS



John McKown

Re: AW: Just curious
(in response to Roger Miller)
Roger and everybody else,
Thanks for all the answers. I understand much better now. I guess that I was
thinking of SQL the same way that I think of COBOL - compile once and run
many times. I guess that it had not penetrated my thick skull that the SQL
statement, in effect, had to be reparsed and validated (more or less,
forgive any errors in terminology) each time the statement was used. I also
did not realize that the SQL standard was so large. I guess that I've only
seen the simplier versions. I've been trying to read the DB2 SQL manuals,
but (forgive me again) they are very sleep-inducing. I much prefer SciFi
<grin>.

BTW - I'm asking all these ignorant questions because we don't have DB2, or
any data base, and I'm trying to gather background in a vain attempt to
convince people that something like DB2 is needed in the long term.
Unfortunately, this is also along with the never ending battle over "Why not
replace OS/390 with Windows?" battle which seems to come up even time the
cost of OS/390 software is compared against the cost of *one* copy of
Windows+PC+software (I.e. 1 windows system is equivalent to 1 OS/390 system
in their mind-set)

----------------------------------------------------------------------
John McKown
Applications and Solutions Team
Healthaxis



> -----Original Message-----
> From: Roger Miller [SMTP:[login to unmask email]
> Sent: Wednesday, January 09, 2002 10:09 AM
> To: [login to unmask email]
> Subject: Re: AW: Just curious
>
> The short answer is that parsing is not nearly that simple. We need to
> comply with the SQL standards, which are now about 1700 pages long, and
> still growing. The keywords are not adequate in themselves and position
> in
> the statement does often have a different meaning.
>
> Roger Miller, DB2 for z/OS
>



James Campbell

Re: AW: Just curious
(in response to Morrill John M)
Otherwise how could
SELECT SELECT FROM FROM ORDER BY ORDER BY SELECT
have an un-ambiguous well-formed meaning? Although I suspect
(I'm not connected at the moment) that DB2 would go down a
wrong track in parsing it.

James Campbell


On 8 Jan 2002 at 16:18, Peter, Georg wrote:

> John,
>
> it works as designed ;-))
>
> With kind regards - mit freundlichen Grüssen,
> Georg H. Peter
> ----------------------------------------------------------------------
> By the way: Unless you are going to retire in 5-10 years you better learn
> Unicode.
>
>
> -----Ursprüngliche Nachricht-----
> Von: McKown, John [mailto:[login to unmask email]
> Gesendet am: Dienstag, 8. Januar 2002 15:57
> An: [login to unmask email]
> Betreff: Just curious
>
> I realize this is not really a DB2 question, per se. But I've got a "why"
> type question. The answer is most likely "because" <grin>. Why must the
> clauses in a SELECT (or UPDATE) command be in any specific order? Since the
> phrases are "key words", why must I always remember to specify FROM before
> WHERE, and WHERE before GROUP BY, and GROUP BY before HAVING, and HAVING
> before ORDER BY. Would it really be that much harder to parse the SQL
> statement if I specified these clauses in any order? I guess that if I did
> SQL a lot, I'd just remember the correct order.
>
> Again, just curious.
>
> ----------------------------------------------------------------------
> John McKown
> Applications and Solutions Team
> Healthaxis
>
>
> the
>
> .
>
>
> the reached at [login to unmask email]