> > I just used Dezign for Databases and created a simple table in what it
> > claimed was "ANSI Level 2" compliance with a one-up "autonum" column.
>
> There is no such animal as "ANSI Level 2" SQL.
That explains a lot of things. About this DDL problem and Dezign for
Databases both.
> > set (n.autonum) = (select (max(autonum),0) + 1 from transaction);
>
> BTW, what are they expecting "(max(autonum),0)" to do, other than draw a
> syntax error? Perhaps there was supposed to be a COALESCE there?
Yeh. Good question. I would assume their generator MEANT
(max(autonum,0)+1) supposing that a "ANSI Level 2"-compliant server's
max function might return the second value if the first is NULL. But no
matter how you slice it, that looks syntactically wrong.
> Even if it worked or were standards-compliant, this approach to
> implementing an autonumbering column would be brain dead in the extreme
> --- you do *not* want to do a max() aggregate for every insert. Use
> a sequence object instead...
Perhaps. Is the sequence object part of standard SQL? I know Postgres and
Oracle have it (among others) but I also know of at least one major
recent-version RDBMS that fails to have a sequence object.
On the topic of max(x), there are certain RDBMSs that implement a max(x)
in such a way that the optimiser will, supposing x has an index on it, use
the index to determine max(x) rather than a table scan. I noticed during
porting an from Sybase to Postgres this particular problem when my select
max(x) from tableY queries were running very slowly.
--
Tim Ellis
DBA, Gamet