Re: Oracle - SQL question embedded select with aggregate function

From: Keith Jamieson <pdkj02_at_email.mot.com>
Date: Thu, 10 Dec 1998 08:40:43 +0000
Message-ID: <366F890B.AFCE4FAB@email.mot.com>

You need to add a group by clause for all columns which are not aggregate functions.As far as I am aware this is standard ANSI SQL

select (select (max(orgid) + 1) from orgs), 0, company, 1, full_desc, effdt,
'12/31/2099' from wrktbl_orgs_9 wk9
Group By 2,company,4,full_desc,effdt,6.

The 2,4,6 are used here because you have hard-coded the values for the 2nd,4th,and 6th columns.

Kelly Penhall-Wilson 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!

  Received on Thu Dec 10 1998 - 02:40:43 CST