How to do...well...anything...in DB2 SQL

[Knut Stolze]

Re: How to do...well...any thing...in DB2 SQL

Ian Boyd wrote:
[color=blue][color=green]
>> As someone
>> else mentioned, in SQL "everything is a table" (a single row is just a
>> special case of a table, and a single value is another such special
>> case).[/color]
>
> Not everything is a table. USER is a special register. There are plenty of
> "special registers".[/color]

When you use USER as an expression in a SQL statement, its value is used.
Where the value originates from is not of interest and the "everything is a
table" also applies to other scalar values, for example constants or values
recieved from an application via host-variables.

A single value (also called scalar value) is the same as a table with one
row and one column. If you try to think that way, statements like

SET (a, b, c) = (1, 2, 3)

are much easier to understand, I'd say.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

> I also respond because there are some more things not correct. So I'd[color=blue]
> rather correct it before someone else gets the wrong ideas in the future.[/color]
[color=blue]
> (I'm wondering, don't you have a database (not necessarily relational) for
> such things?)[/color]

Google groups! My database search will be "ian boyd db2 create table", and
brought right to valid DB2-SQL example to create a table, etc.
[color=blue]
> This is because the special registers are considered to be not
> deterministic. A different user connecting to the system implies
> different
> values for those defaults. So it _is not_ deterministic. So use a
> trigger
> instead.
> You could argue that the same holds for CURRENT TIMESTAMP and I would
> agree.
> However, the user cannot influence the current timestamp, so DB2 can
> safely
> determine it when a row is inserted.[/color]

Well...my post isn't wrong. DB2 can't do it. Maybe it has a reason for it,
maybe not. Either way, it can't be done that way.
[color=blue]
> (1) I would throw away the procedural logic for the "SavedUserI D" and do
> this purely in SQL as we discussed before.
> (2) You should switch to a FOR EACH ROW trigger as Serge explained.[/color]

It's also a reference of how to do things. i'll will follow your suggestions
in the final implementation.
[color=blue]
> Not true. DB2 does support comments:[/color]
Turns out that the Windows ODBC or OLEDB provider strip CRLF's. So this
destroys inline comments. You can argue that the driver is not DB2, but in
the end i cannot use inline comments. If IBM will update the drivers so it
works, we can then use them - but now we cannot.
[color=blue][color=green]
>> 3. DB2 cannot implicitly cast a NULL to any data type.[/color]
> It does if it can derive the data type, for example from the column name
> or
> by other means like here:[/color]

In this case DB2 *should* be able to derive the data type, but won't:
INSERT INTO Users (Username, Address)
SELECT uid, NULL FROM Employees

In this case it does
INSERT INTO Users (Username, Address)
VALUES ('Ian', NULL)

So if you ever deal with nulls, it's safer to just cast it. Save time by not
going round and round trying to guess what db2 will and won't do. That's my
point there.
[color=blue]
> <Celko-mode>NULL is not a value.</celko mode>[/color]
NULL is the absense of database. If i personally don't think DB2 should
enforce a type on nothing. But here we get into deep philosophical ideas
(What does nothing sound like? It can sound like whatever you want. Ooooo)
[color=blue]
> Not true. See above.[/color]
See above :)
[color=blue]
> Not true. See above.[/color]
See above :)
[color=blue]
> This is not a SQL construct. ;-)[/color]
Change your definition :)
[color=blue][color=green]
>> 7. Every DB2-SQL Statement must end with a semi-colon (;)[/color]
> Not true. You can choose your statement terminator freely, and it can
> even
> be the end of line.[/color]
Well *i* can't.
[color=blue][color=green]
>> 8. You cannot give DB2 some arbitrary SQL to run. You are only allowed to
>> give DB2 one "statement" at a time. If you try to give it more than one
>> statement, it will choke. Examples of statements include CREATE TABLE,
>> CREATE TRIGGER, CREATE PROCEDURE, DECLARE CURSOR, CALL (which executes a
>> stored procedure).[/color]
>
> Not true. You have to separate the statements with the statement
> terminator.[/color]
See above :)
[color=blue][color=green]
>> The major cavaet with this limitation is that something like the
>> following
>> is invalid:
>>
>> CREATE TABLE Users (
>> UserID int,
>> Username varchar(50);[/color]
>
> Closing ')' is missing.[/color]
Pwned.

[Brian Tkatch]

Re: How to do...well...any thing...in DB2 SQL

> Then, ping-pong between the explanation and the SQL diagram in order to try to infer what the syntax is.

Not infer. the diagram is clear, according to the guidelines presented
in "About this book" subsection "How to read the syntax diagrams".

You may not like the IBM diagrams, but they are indeed very clear.

B.

[Brian Tkatch]

Re: How to do...well...any thing...in DB2 SQL

>From almost day one, i got burned fetching column values by ordinal. It's[color=blue]
>just a bad idea. Yes i could do it, and i could be forced to keep the
>ordinal location of every field in every table, result set, or query.[/color]

Not in the TABLE, in the query. And the query changes per application,
so no problem. If the query is in a stored PROCEDURE, and outputs a
result set, it is best to use the VALUES inside a SELECT statement as
Knut pointed out.

B.

[Gert van der Kooij]

Re: How to do...well...any thing...in DB2 SQL

In article <dupi4p030la@en ews3.newsguy.co m>, Ian Boyd (ian.msnews010
@avatopia.com) says...[color=blue][color=green]
> > This isn't right, it depends. If you put multiple commands in one
> > file you need to seperate them if you keep them on one line[/color]
>
> Nobody here will write queries on all one line. It's jut a practical thing.
>[color=green]
> > 'create function' and 'create trigger' statements needs to be
> > seperated by another delimiter.
> > connect to <yourdb> @[/color]
>
> i am already connected using ADO. The "connect to <yourdb> @" is invalid
> DB2-SQL.
> If i need to separate statements, i'm going to have to do it manually.
>
>[/color]

Which tool are you using to run your SQL?
I guess I'm missing the overall picture, did you post it somewhere in
this thread?

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

> You may not like the IBM diagrams, but they are indeed very clear.

The same diagrams are in the ANSI spec. They are very clear once you already
know the syntax.

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

> And the query changes per application,[color=blue]
> so no problem.[/color]

Not when a query is the query was written in one application and used by
another.
[color=blue]
> If the query is in a stored PROCEDURE, and outputs a
> result set, it is best to use the VALUES inside a SELECT statement as
> Knut pointed out.[/color]

How do you give names to the fields returned from a SELECT of a VALUES
table?

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

> A single value (also called scalar value) is the same as a table with one[color=blue]
> row and one column. If you try to think that way, statements like[/color]

That doesn't solve the inconsistency of sometimes being able to use scalars
and sometimes not, and sometimes being able to use tables and sometimes not.

i'm less pointing out the "what" of db2 and more of the "why." i'm fighting
less with the 'this is the way it is' and more of the 'why is it this way?'

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

> Which tool are you using to run your SQL?[color=blue]
> I guess I'm missing the overall picture, did you post it somewhere in
> this thread?[/color]

i'm using a variety of tools in a variety of environments. None of them
involve a command line and runnnig saved files containing SQL statements.
Most involve writing a query in an editor of some sort and running it. Or
then taking that query and embedding it into code. And we prefer our queries
to be nicely formatted and (ideally) commented.

It is safe to say that our tools are Windows desktop machines, Windows
applications, Windows services, Websphere Java Servlets, editors that let us
type in and run SQL.

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

> I think Ian might have written this before discovering (in a separate[color=blue]
> note somewhere in this thread) that something on the client side is
> stripping line breaks causing problems with line comments.[/color]

It is important to note that it is either inside IBM's ODBC driver or IBM's
OLEDB provider. When we use the same tools to send queries to Microsoft's
SQL Server using either Microsoft's ODBC driver or Microsoft's OLEDB
provider, carriage returns and are put onto the ethernet intact. So it is
something to do with DB2. Maybe not the server side, but it's still IBM's
solution. And so, with IBM's solution i generally cannot use comments.

It really is irrelavant if DB2 is choking on it because it received mangled
SQL, IBMs drivers did it. They are DB2 drivers. Hence DB2 did it. If the
roles were reversed, it would be an SQL Server problem.
[color=blue][color=green][color=darkred]
>> > 8. You cannot give DB2 some arbitrary SQL to run. You are only
>> > allowed to give DB2 one "statement" at a time. If you try to give
>> > it more than one statement, it will choke. Examples of statements
>> > include CREATE TABLE, CREATE TRIGGER, CREATE PROCEDURE, DECLARE
>> > CURSOR, CALL (which executes a stored procedure).[/color]
>>
>> Not true. You have to separate the statements with the statement
>> terminator.[/color]
>
> Actually, Ian is correct in a limited sense here. While it is certainly
> true that one can write a script containing several statements
> (separated by whatever means one wishes) and give this to the CLP for
> execution, this still doesn't mean you can execute multiple
> statements...[/color]

Yes. The tools can do me a favor and chop up a bunch of SQL into chunks that
DB2 can manage. It would be much nicer if DB2 could manage all the code at
once. And as we've already seen in other database products, there is no
technical limitation to doing so - just a design one.

So change the design. People can still be free to only send one statement to
DB2 at a time. But now DB2 can also be powerful and intelligent enough to
handle more than one statement.

[color=blue]
>Quoting from Microsoft's own documentation of the SQLExecDirect call in
>the ODBC API:
> [snip]
> No mention of multiple statements there.[/color]

From the Microsoft Books Online:
<quote>
Batches
A batch is a group of one or more Transact-SQL statements sent at one time
from an application to Microsoft® SQL Server™ for execution. SQL Server
compiles the statements of a batch into a single executable unit, called an
execution plan. The statements in the execution plan are then executed one
at a time.
....
Assume there are 10 statements in a batch. If the fifth statement has a
syntax error, none of the statements in the batch are executed. If the batch
is compiled, and the second statement then fails while executing, the
results of the first statement are not affected because it has already
executed.
</quote>

Here is a phrase that i really take issue with:[color=blue]
> If one can do this with MS SQL
> Server, it's certainly non-standard behaviour, and shouldn't be
> relied upon to be implemented by other databases.[/color]

SQL Server is easier to *use* because of it. DB2 is free to keep their
product harder to use.
Pretend it didn't work in SQL Server. Pretend that this wasn't the way it
works. Why shouldn't IBM innovate and include such a feature? Why not look
for reasons to DO new features, rather than looking for reasons NOT to do
new features.

An overarching theme i'm trying to express is usability. Make things more
usable, rather than less. Just because that's they way it is doesn't mean
that's the way it has to be. There are things that SQL Server does that are
completely self-consisent with their own little world of rules - but they're
just stupid. Just make it work! We all know what you were trying to do. Why
don't you do it? Is it a technical limitation? Fix it! Is it new idea that
you can't stick into your model of how the product is supposed to work?
Maybe the model is broken, maybe you need to extend it!

And it's many many things. It's not just defaults on columns. It's not just
implicitly casting. It's not just being able to select a scalar. It's a lot
of things.

Another very very very common example that people ask over and over and over
and OVER in the Microsoft newsgroups:

"How come i can't do:

SET MyVariable = CALL MyStoredProcedu re

when my procedure only returns one row and one column?"


We all know what the person was trying to do. We all KNOW it. So, now before
going into any arguments about why what he is doing is wrong, you have to
stop and choose: Are we going to try to helpful, or are we going to be a
hinderance?

Do you want to make that syntax work, but you can't for technical reasons?
Fix them!
Are you going to refuse to allow that syntax on moral grounds (i.e. Celko)?
You can do that. But perhaps another database vendor will incorporate that
functionality into their system, and theirs will become easier to use.



i realize DB2 is trying to follow the standards, and standards are a good
thing. But please don't argue with your users telling them they need to do
things the hard way because it's "standard" or because "we've decided how
you should do things." Innovate!

[Dave Hughes]

Re: How to do...well...any thing...in DB2 SQL

Ian Boyd wrote:
[color=blue][color=green][color=darkred]
> > > - IBM Command Editor (db2ce.bat) with it's "Statement termination
> > > character" changed to =[/color]
> >
> > Hmmm, using = as a statement terminator is probably a bad idea
> > (given the ambiguity).[/color]
>
> Sorry, sorry, sorry. That didn't translate through the news server
> properly. The character i changed it to was the "Vulgar Fraction
> One Half" U+00BD
> Alt+0189 on the keyboard if you're using Windows
> "=" <-- It shows up as 1/2 in Outlook Express's editor :)[/color]

Argh! I was scratching my head wondering how anyone could be so crazy
as to use equals as a statement terminator, but from experience I
should have known better (being in the UK, I'm used to mail and news
servers translating the British pound sign into =3A or just =)!
[color=blue][color=green]
> > Hmmm ... you shouldn't need those CASTs around the NULLs, not in an
> > INSERT statement anyway. Let me just try it:[/color]
>
> You do - sometimes.
>
> Try something of the form:
> INSERT INTO TestTable (Name, Address, Phone)
> SELECT fname, addr1, NULL FROM Customers[/color]

Damn, I'd forgotten about that particular construction.

[snip][color=blue]
> Now in this case, DB2 actually can infer the type - it just isn't
> looking far enough ahead. So rather than risk it sometimes working
> and sometimes not, i will just adhere to the rule that you should
> always do it. It's just safer that way.[/color]

Fair point ... probably a better strategy for someone new to DB2 and
just wishing to "get on with it".
[color=blue][color=green]
> > In this case it's because a NULL on its own has no datatype, and
> > each column in a query result must have a datatype. Ergo, the NULL
> > must be cast to some datatype in this particular case.[/color]
>
> No reason DB2 can't just pick a type. If the extent of my statement
> was: SELECT NULL AS SomeNullValue FROM sysibm.sysdummy 1
>
> Then just go ahead and make it an integer. Nobody's gonna care.[/color]

Pick an arbitrary data type... Certainly a viable option, but I'm
beginning to wonder...
[color=blue][color=green]
> > Incidentally, this often causes confusion with the set operators
> > (UNION, INTERSECT and EXCEPT). For example (again using the TEST
> > table from above):
> >
> >db2 => SELECT A, B, C FROM TEST
> >db2 (cont.) => UNION
> >db2 (cont.) => SELECT NULL, B, C FROM TEST;
> > SQL0206N "NULL" is not valid in the context where it is used.
> > SQLSTATE=42703[/color]
>[color=green]
> > One could argue that, in this case DB2 ought to be able to figure
> > out that the NULL in the query on the right-hand side of the UNION
> > should be implicitly cast to an INTEGER as that is the datatype of
> > the first column in the query on the left-hand side of the UNION.[/color]
>
> Yes, one would :)[/color]

Yes, there's that idea again: "infer the type".

<tangent>

The more I look at SQL (ignoring the hybrid stuff like stored
procedures, triggers, and dynamic compound statements), the more I'm
convinced it's just a set-based pure functional programming language in
disguise (the disguise being a truly bizarre syntax that wraps a whole
bunch of operations up in a single expression called SELECT).

The type inferencing mentioned above is exactly what certain pure
functional languages like ML do. Which makes me wonder, in the case of
picking an arbitrary datatype (as mentioned above), whether it wouldn't
be a good idea to steal an idea from functional programming (e.g.
permit a column in an output set with "arbitrary" type). Yeah, I know
.... crazy talk ...

</tangent>

Now returning to our scheduled program...

[snip another type inference example][color=blue]
>[color=green]
> > Hmm, I'd say there's very little in DB2 that's non-sensical, and
> > generally I do find there is a philosophy behind DB2's way of doing
> > things. But as I mentioned in another post, I'm beginning to
> > understand just how alien it must seem when "switching
> > philosophies" so to speak.[/color]
>
> It's not so much switching that is a problem, or maybe it is. Maybe
> it is the design standard itself that is weird. But there are things
> that "work there", but "don't work there". And often-times the answer
> as to why it behaves that way is: "that's the standard" "by
> design"
>
> But as a human using the system there are things that just shouldn't
> be that way. If the standard says it, maybe the standard needs to be
> revisited. If it's a technical limitation, then it needs to be
> overcome. If what i want makes no logical sense, then there will be a
> logical reason why. But if it turns out that "i want to do this, i
> can't think of any reason why your product can't shouldn't do it."
> "Well, we don't, and we have no plans to do it that way." "Then
> can you point to me to a competitors product that will do this?"
>
> On some level, IBM is writing software to be used by developers. It
> might not be a bad idea to make their jobs easier, rather than harder.[/color]

Again, fair point.
[color=blue]
>[color=green]
> > The VALUES expression, to me, is a good example of "DB2's
> > philosophy". A number of other databases use
> >
> > SELECT <value>, <value>, ...
> >
> > as a way of generating a row on an adhoc basis. However, it's
> > unclear with this syntax how one could generate an adhoc table.[/color]
>
> Yes, i agree. VALUES in an expression that has no equivalent in SQL
> Server. And IBM has added value to their product with this
> innovation. And other RDBMs would do well to steal the idea :)[/color]

Actually, I'm not entirely sure VALUES is a DB2 "innovation " ... it
could just be standard SQL that DB2's implemented and which other
databases have ignored. Anyone know for sure?
[color=blue][color=green]
> > As someone
> > else mentioned, in SQL "everything is a table" (a single row is
> > just a special case of a table, and a single value is another such
> > special case).[/color]
>
> Not everything is a table. USER is a special register. There are
> plenty of "special registers". i guess i would need to ask, since i
> am speaking without knowing... Is the following valid:
>
> ALTER TABLE MyTable ALTER COLUMN Tag SET WITH DEFAULT asdfasdf ;
> how about
> ALTER TABLE SUPERDUDE.AUDIT _LOG ALTER COLUMN TAG SET WITH DEFAULT
> USER ; how about
> ALTER TABLE SUPERDUDE.AUDIT _LOG ALTER COLUMN TAG SET WITH DEFAULT
> (select username from employees fetch first 1 rows only) ;[/color]

Granted, I should qualify that over-generalizing statement: "In SQL
everything is a table ... except when it's not" :-)

In general, SQL could be said to have three "meta-types": scalars,
tuples, and sets of tuples (well, strictly speaking, bags of tuples as
one can have a table without a unique key, but we'll stick with "set"
as per convention). However, a scalar is still a special case of a
one-element tuple, and a tuple a special case of a one-element set.

Which is not to say that one can use a set of tuples anywhere a scalar
is required, but you can still see how a scalar is a "special case" of
a set of tuples.

Anyway, to the above statements. The first two are fine (with the
exception that the constant asdfasdf ought to be quoted). Column
defaults must be scalar, and must either evaluate to a constant or use
a special register as their only variable component, and both
expressions meet these criteria. (I recall from a prior post that not
all special registers, i.e. CURRENT CLIENT_APPLNAME etc., can currently
be used in this manner, however that turned out to be because of a lack
of implementation rather than any fundamental technical or theoretical
restriction).

The third statement, however, is problematic. It's not going to work
because it doesn't evaluate to a constant or special register. That's
the primary reason it won't work, but there's another aspect to it that
begs comment:

FETCH FIRST n ROWS is an oft abused modifier. As I understand it
(hopefully, someone more knowledgeable will correct me if I'm wrong
about this), FETCH FIRST n ROWS is meant to be used to grab a quick
sample of data from a potentially long running query.

For example, if you've put together a query that might take a *long*
time to run, and you'd just like to check a sample of the output, you
add FETCH FIRST n ROWS. I don't believe it's meant to be used in the
sense of limiting a query to a single row, or the top n results, or
whatever. This is simply because a table (theoretically at least) has
no intrinsic order and therefore without an ORDER BY clause in the
query one cannot guarantee the result is deterministic. Even with an
ORDER BY clause, this still wouldn't be the "right" to perform these
tasks.

The right way of limiting a query result to one row would be to ensure
that the WHERE clause specifies enough conditions to guarantee a unique
match in the table (i.e. by limiting on all columns of a unique key).

Likewise, the right way of grabbing the top n results would be to use
the RANK() or DENSE_RANK() functions. Unfortunately, the documentation
for the OLAP functions (of which RANK and DENSE_RANK are two) seems to
have been moved somewhere obscure in the Info Center (they're now all
buried in the "Reference / SQL / Language elements / Expressions"
subject). Just search for DENSE_RANK and you should find them easily
enough.

[snip][color=blue]
> It's difficult to be frustrated and pleasent at the same time;
> especially when i also turn around and vent off to colleagues here so
> easily :)[/color]

I recommend several therapeutic sessions of fragging friends and
colleagues in Quake III (honestly, why this isn't considered mandatory
for the mental well-being of office workers is beyond me :-)


Dave.

--

[Dave Hughes]

Re: How to do...well...any thing...in DB2 SQL

Ian Boyd wrote:
[color=blue][color=green]
> > And the query changes per application,
> > so no problem.[/color]
>
> Not when a query is the query was written in one application and used
> by another.
>[color=green]
> > If the query is in a stored PROCEDURE, and outputs a
> > result set, it is best to use the VALUES inside a SELECT statement
> > as Knut pointed out.[/color]
>
> How do you give names to the fields returned from a SELECT of a
> VALUES table?[/color]

I'm pretty sure I've provided the syntax for this in a couple of other
comments, but maybe I didn't indicate it explicitly:

SELECT COLA, COLB, COLC
FROM (
VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)
) AS TEMP(COLA, COLB, COLC)

The "AS" bit is mandatory and provides a name for the table produced by
VALUES. The field aliases in brackets after AS TEMP are optional, but
allow you to assign names to the columns in the VALUES table, which can
then be referenced in the enclosing SELECT statement as above. Though I
could just as easily have done:

SELECT *
FROM (
VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)
) AS TEMP(COLA, COLB, COLC)


HTH,

Dave.

--

[Knut Stolze]

Re: How to do...well...any thing...in DB2 SQL

Dave Hughes wrote:
[color=blue]
> The third statement, however, is problematic. It's not going to work
> because it doesn't evaluate to a constant or special register. That's
> the primary reason it won't work,[/color]

Actually, standardized SQL allows check constraints to contain sub-selects.
None of the products I know of actually implemented this, however. The
issue is probably (a) starting table scans on an insert is usually not such
a good idea, and (b) if it is really necessary, one could use triggers. So
it boils down to question we always have with new functionality: Who's
gonna pay for it and which feature should be moved back instead?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

> I'm pretty sure I've provided the syntax for this in a couple of other[color=blue]
> comments, but maybe I didn't indicate it explicitly:
>
> SELECT *
> FROM (
> VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)
> ) AS TEMP(COLA, COLB, COLC)
>
> The "AS" bit is mandatory and provides a name for the table produced by
> VALUES. The field aliases in brackets after AS TEMP are optional, but
> allow you to assign names to the columns in the VALUES table, which can
> then be referenced in the enclosing SELECT statement as above. Though I
> could just as easily have done:[/color]

Thank you for that. i do remember seeing the post, but i thought that
the COLA, COLB, COLC were placeholders for something...i dunno.
i didn't appreciate that it is the way to give names to the columns
created by VALUES.

VALUES is a cool construct. i don't know if it's in SQL92/99/etc, but
SQL Server should have something like it - even if i can't think of good
uses for it right now :)

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

> Argh! I was scratching my head wondering how anyone could be so crazy
Don't put it past me :)
[color=blue][color=green][color=darkred]
>> > Hmmm ... you shouldn't need those CASTs around the NULLs, not in an
>> > INSERT statement anyway. Let me just try it:[/color]
>> You do - sometimes.
>> INSERT INTO TestTable (Name, Address, Phone)
>> SELECT fname, addr1, NULL FROM Customers[/color]
> Damn, I'd forgotten about that particular construction.[/color]
Bam! Boo-yeah!
[color=blue]
> Fair point ... probably a better strategy for someone new to DB2 and
> just wishing to "get on with it".[/color]
That's the sentiment DB2 needs to ponder on for a little while.
[color=blue]
> Pick an arbitrary data type... Certainly a viable option, but I'm
> beginning to wonder...[/color]
i mean, it has two choices: go ahead and just do what we all know i was
trying to do, or refuse. No harm in db2 being nicer and easier.

[color=blue]
> <tangent>
> The more I look at SQL...
> ...makes me wonder...
> ...whether it wouldn't
> be a good idea to steal an idea from functional programming..
> </tangent>[/color]
i originally learned SQL as ANSI-SQL from a very good book explaining
SQL. It wasn't expousing ANSI-SQL over other flavors, just teaching SQL.
SQL is, to me, a very intuitive thing, and playing with the examples from
the book originally in SQL Server 6.5 almost 10 years ago, it was very
easy to use. i am all in favor of adding to SQL intuitive extensions.
[color=blue]
> Actually, I'm not entirely sure VALUES is a DB2 "innovation " ... it
> could just be standard SQL that DB2's implemented and which other
> databases have ignored. Anyone know for sure?[/color]
i've never seen it through SQL Server 2000. But i hear that MSSQL has been
playing ANSI catchup for the last few years - not that not begin
fully ANSI-compliant is deal-breaker - just bragging rights.
[color=blue]
> Granted, I should qualify that over-generalizing statement: "In SQL
> everything is a table ... except when it's not" :-)[/color]
Goes back to the "you know what i wanted to do, so why are you
fighting me on this" sentiment.
[color=blue]
> The third statement, however, is problematic. It's not going to work
> because it doesn't evaluate to a constant or special register. That's
> the primary reason it won't work, but there's another aspect to it that
> begs comment: FETCH FIRST n ROWS[/color]

It was a contrived example, but one that shows how things are not always
consistent.
[color=blue]
> I recommend several therapeutic sessions of fragging friends and
> colleagues in Quake III (honestly, why this isn't considered mandatory
> for the mental well-being of office workers is beyond me :-)[/color]

Right now it's WoW.