Re: Oracle - SQL question embedded select with aggregate function

In article <74nai3$42e$1_at_pollux.dnai.com>,   "Kelly Penhall-Wilson" <kpw_at_seekersoft.com> wrote:
> The following select statement is giving me some problems in Oracle, yet it
> is valid in both MSSQL server and Sybase SQL server.
>
> select (select (max(orgid) + 1) from orgs), 0, company, 1, full_desc, effdt,
> '12/31/2099' from wrktbl_orgs_9 wk9
>
> The problem, I believe, is the embedded select of the select max(orgid).
> I'm trying to make a generic sql statement to run on multiple platforms and
> generate a unique number by adding 1 to the max column value.
>
> Any insights would be most appreciated!
>
> -----------------------------
> Kelly Penhall-Wilson
> Application Developer
> Seeker Software, Inc.
> (510) 587-0542 voice
> (510) 433-9329 fax
> kpw_at_seekersoft.com

If you are really trying to generate generic cross-platform queries, you really should get familiar with the SQL standard. There are different levels of compliance to the standard. Queries written for higher levels of complience will likely not run on products with lower levels of compliance.

ORACLE, having been developed before the original SQL standard, conforms to the standard at the lowest level (level 1 of 3).

In ORACLE you will have to do this in a subquery:

select orgview.org_id_max, 0, company, 1, full_desc, effdt, '12/31/2099'  from wrktbl_orgs_9 wk9,

      (select (max(orgid) + 1) org_id_max from orgs) orgview  ;

There is a small paperback book which summarizes and paraphrases the SQL standard. (My copy is home and I can't remember the author or exact title.) It is worth the investment, especially in your situation.

Good luck. Let me know If I can help further.

--
Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Dec 10 1998 - 13:04:23 CST