Problem with views

From: Guenter Gersdorf <G.Gersdorf_at_tu-bs.de>
Date: 1995/11/29
Message-ID: <49hl2u$9od_at_ra.ibr.cs.tu-bs.de>#1/1

I create the following table and view and got an error:

---

SQL> CREATE TABLE SST (
  2 ID number default USERENV('SESSIONID') not null,   3 VAL number
  4 );
Table created.
SQL> CREATE VIEW T AS SELECT
  2 VAL
  3 FROM SST WHERE ID=USERENV('SESSIONID'); View created.

SQL> 
SQL> 
SQL> INSERT INTO T VALUES(1);

1 row created.
SQL> INSERT INTO T VALUES(2);
1 row created.
SQL>
SQL> SELECT SUM(VAL) FROM T;  SUM(VAL)

---

        3

SQL> --Original query:
SQL> INSERT INTO T (VAL) SELECT SUM(VAL) FROM T; INSERT INTO T (VAL) SELECT SUM(VAL) FROM T
*

ERROR at line 1:
ORA-00937: not a single-group group function

SQL> --equiv to:
SQL> INSERT INTO SST (id, VAL) SELECT USERENV('SESSIONID'), SUM(VAL) FROM SST; INSERT INTO SST (id, VAL) SELECT USERENV('SESSIONID'), SUM(VAL) FROM SST
*

ERROR at line 1:
ORA-00937: not a single-group group function

SQL> --but this works:
SQL> INSERT INTO SST (id, VAL) SELECT MIN(USERENV('SESSIONID')), SUM(VAL) FROM SST WHERE ID=USERENV('SESSIONID'); 1 row created.

SQL>
SQL> SELECT * FROM T;       VAL

---

        1
        2
        3

SQL>

---

If the references to USERENV('SESSIONID') is replaced by a constant, all works ok. It even works if this is replaced by UID, which is also a function! Why is this and what can i do, so that my original query works??

(This is Oracle Server 7.0.13.1.0 on AIX 3.2.5) Guenter

---

Guenter Gersdorf                         Phone:      +49/(0)531/391-7634
Inst. f. Werkzeugmaschinen               Fax:                      -5842
und Fertigungstechnik, TU Braunschweig   E-Mail:     G.Gersdorf_at_tu-bs.de

Langer Kamp 19b, D-38106 Braunschweig http://www.iwf.ing.tu-bs.de/~gg Received on Wed Nov 29 1995 - 00:00:00 CET