> > 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
