ORA-00934: group function is not allowed here

ORA-00934: group function is not allowed here

Post by Mark MacRa » Sat, 24 Feb 2001 06:05:50



I am trying to insert a value based on the current max of a column, but am
getting the ORA-00934 error.

The statement I'm trying basically looks like:

INSERT INTO table (column) VALUES (nvl(max(column) + 1, 1))

(i.e. if no data, start at 1, otherwise add 1 to the current max)

So apparently it can't be done like this - how best can this be done without
the possibility that another client comes in in between calls (i.e. if I
first made a call to see what the max is, then the next call would be the
insert)

TIA,
mark

 
 
 

ORA-00934: group function is not allowed here

Post by Graves, Don [FITZ2:8M72:EXCH » Sat, 24 Feb 2001 05:45:57


I'm fairly new to the world of Java.  I have a problem and I have been searching
all day for an answer without luck.  Perhaps someone with more knowledge can
help me.

I have:
    - Solaris box
    - JDK 1.1.6
    - JDK 1.3 installed in another directory
    - Oracle 8.1.5
    - DB.java - a class that does the work involved with connecting to a test
database
    - TEST.java - a main program class that uses DB to fetch data from the test
database
    - everything is on the solaris box

The java program compiles and runs properly when I use javac/java version 1.1.6

The java program compiles with the 1.3 version of javac, but when I run it, I
get this:

% java Test
Exception in thread "main" java.lang.UnsatisfiedLinkError: make_c_state
        at oracle.jdbc.oci8.OCIDBAccess.make_c_state(Native Method)
        at oracle.jdbc.oci8.OCIDBAccess.logon(OCIDBAccess.java)
        at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java)
        at
oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java)
        at java.sql.DriverManager.getConnection(DriverManager.java:517)
        at java.sql.DriverManager.getConnection(DriverManager.java:177)
        at DB.<init>(DB.java:18)
        at Test.main(Test.java:11)
%

I am pretty sure that $CLASSPATH and $LD_LIBRARY_PATH are correct.

I thought about providing the source code in this post, but I decided not to
since it works properly under JDK 1.1.6.

$ORACLE_HOME/jdbc/lib contains the files:
    classes111b.zip
    nlscharset11.zip

When I switch between JDK 1.1.6 to 1.3, I alter $path, $CLASSPATH, and
$LD_LIBARY_PATH as needed.

I have a feeling that my problem is that Oracle doesn't have a driver for JDK
1.3.   According to http://technet.oracle.com/tech/java/sqlj_jdbc/, these are
the available drivers:

       Download the drivers Solaris
             Oracle8i 8.1.7 JDBC Drivers for use with JDK 1.2.x for Solaris
             Oracle8i 8.1.7 JDBC Drivers for use with JDK 1.1.x for Solaris
             Oracle8i 8.1.6.2.0 JDBC Drivers for use with JDK 1.2.x for Solaris
             Oracle8i 8.1.6.2.0 JDBC Drivers for use with JDK 1.1.x for Solaris
             Oracle 8i (8.1.5) JDBC Drivers for use with JDK 1.1.x for Solaris
             Oracle 8i (8.1.5) JDBC Drivers for use with JDK 1.0.2 for Solaris

There is no mention is JDK 1.3.  Is this because an oracle driver for it doesn't
exist yet?

I apologize if these are simple simon questions, but I've been trying for hours
to find the answer to my problem.

Thanks,
Don

 
 
 

ORA-00934: group function is not allowed here

Post by AV » Sat, 24 Feb 2001 08:09:46


apparently, classes111.zip are for 1.1.x. There are
classes12.zip (classes12_01.zip) designed for work
with 1.2.x and 1.3.

I do remember that THIN driver from classes111.zip
worked ok with jdk1.2.2 but I have no OCI driver
experience. OCI uses some native libraries than can
be more sensitive to jdk version.

Thus, you was in right place to download new drivers...

AlexV



Quote:> I'm fairly new to the world of Java.  I have a problem and I have been
searching
> all day for an answer without luck.  Perhaps someone with more knowledge
can
> help me.

> I have:
>     - Solaris box
>     - JDK 1.1.6
>     - JDK 1.3 installed in another directory
>     - Oracle 8.1.5
>     - DB.java - a class that does the work involved with connecting to a
test
> database
>     - TEST.java - a main program class that uses DB to fetch data from the
test
> database
>     - everything is on the solaris box

> The java program compiles and runs properly when I use javac/java version
1.1.6

> The java program compiles with the 1.3 version of javac, but when I run
it, I
> get this:

> % java Test
> Exception in thread "main" java.lang.UnsatisfiedLinkError: make_c_state
>         at oracle.jdbc.oci8.OCIDBAccess.make_c_state(Native Method)
>         at oracle.jdbc.oci8.OCIDBAccess.logon(OCIDBAccess.java)
>         at

oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java)

- Show quoted text -

Quote:>         at
> oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java)
>         at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java)
>         at java.sql.DriverManager.getConnection(DriverManager.java:517)
>         at java.sql.DriverManager.getConnection(DriverManager.java:177)
>         at DB.<init>(DB.java:18)
>         at Test.main(Test.java:11)
> %

> I am pretty sure that $CLASSPATH and $LD_LIBRARY_PATH are correct.

> I thought about providing the source code in this post, but I decided not
to
> since it works properly under JDK 1.1.6.

> $ORACLE_HOME/jdbc/lib contains the files:
>     classes111b.zip
>     nlscharset11.zip

> When I switch between JDK 1.1.6 to 1.3, I alter $path, $CLASSPATH, and
> $LD_LIBARY_PATH as needed.

> I have a feeling that my problem is that Oracle doesn't have a driver for
JDK
> 1.3.   According to http://technet.oracle.com/tech/java/sqlj_jdbc/, these
are
> the available drivers:

>        Download the drivers Solaris
>              Oracle8i 8.1.7 JDBC Drivers for use with JDK 1.2.x for
Solaris
>              Oracle8i 8.1.7 JDBC Drivers for use with JDK 1.1.x for
Solaris
>              Oracle8i 8.1.6.2.0 JDBC Drivers for use with JDK 1.2.x for
Solaris
>              Oracle8i 8.1.6.2.0 JDBC Drivers for use with JDK 1.1.x for
Solaris
>              Oracle 8i (8.1.5) JDBC Drivers for use with JDK 1.1.x for
Solaris
>              Oracle 8i (8.1.5) JDBC Drivers for use with JDK 1.0.2 for
Solaris

> There is no mention is JDK 1.3.  Is this because an oracle driver for it
doesn't
> exist yet?

> I apologize if these are simple simon questions, but I've been trying for
hours
> to find the answer to my problem.

> Thanks,
> Don

 
 
 

ORA-00934: group function is not allowed here

Post by sjenni » Sat, 24 Feb 2001 09:08:02


Hi Mark,

Why don't you use an Oracle sequence?

Simon

 
 
 

ORA-00934: group function is not allowed here

Post by Heinz Hube » Sat, 24 Feb 2001 16:57:37



> I am trying to insert a value based on the current max of a column, but am
> getting the ORA-00934 error.

> The statement I'm trying basically looks like:

> INSERT INTO table (column) VALUES (nvl(max(column) + 1, 1))

> (i.e. if no data, start at 1, otherwise add 1 to the current max)

> So apparently it can't be done like this - how best can this be done without
> the possibility that another client comes in in between calls (i.e. if I
> first made a call to see what the max is, then the next call would be the
> insert)

You have to use a select in the insert:
INSERT INTO table (column, other)
 SELECT NVL(MAX(column) + 1, 1)), :other (or 'other')
  FROM table;

The other columns are simply bound like a prepared statement or hard
coded into the select.

hth,
Heinz

 
 
 

ORA-00934: group function is not allowed here

Post by Mark MacRa » Sat, 24 Feb 2001 21:55:11


Quote:> > INSERT INTO table (column) VALUES (nvl(max(column) + 1, 1))

> You have to use a select in the insert:
> INSERT INTO table (column, other)
>  SELECT NVL(MAX(column) + 1, 1)), :other (or 'other')
>   FROM table;

Thanks - I was thinking that I would probably have to do something like
that, but being no database expert, didn't know how it would work.

I notice that you didn't include 'VALUES' - isn't this required on an
INSERT?

 
 
 

ORA-00934: group function is not allowed here

Post by Heinz Hube » Sat, 24 Feb 2001 23:17:01



> > > INSERT INTO table (column) VALUES (nvl(max(column) + 1, 1))

> > You have to use a select in the insert:
> > INSERT INTO table (column, other)
> >  SELECT NVL(MAX(column) + 1, 1)), :other (or 'other')
> >   FROM table;

> I notice that you didn't include 'VALUES' - isn't this required on an
> INSERT?

There are two forms of supplying the values to INSERT:
- with VALUES like INSERT INTO table (column) VALUES (some_value)
- with SELECT like I did above; This can also insert more than one row!!

Cheers,
Heinz

 
 
 

ORA-00934: group function is not allowed here

Post by Mark MacRa » Sat, 24 Feb 2001 23:57:37


Quote:> There are two forms of supplying the values to INSERT:
> - with VALUES like INSERT INTO table (column) VALUES (some_value)
> - with SELECT like I did above; This can also insert more than one row!!

Ah, learn something everyday :) Well now how do I insert the values then?
Like the strings and dates and longs for the other columns in the insert?

I can't:
INSERT INTO table (column1, column2, column3) SELECT 'value1', 4523.12,
nvl(max(column3) +1, 1) from table

 
 
 

ORA-00934: group function is not allowed here

Post by Heinz Hube » Tue, 27 Feb 2001 17:25:34



> > There are two forms of supplying the values to INSERT:
> > - with VALUES like INSERT INTO table (column) VALUES (some_value)
> > - with SELECT like I did above; This can also insert more than one row!!

> Ah, learn something everyday :) Well now how do I insert the values then?
> Like the strings and dates and longs for the other columns in the insert?

> I can't:
> INSERT INTO table (column1, column2, column3) SELECT 'value1', 4523.12,
> nvl(max(column3) +1, 1) from table

Why not? As long as you want to insert only one row, this would be the
correct way to do it.
Do you get any errors?

If you don't want to supply the values as constants or if you have to
insert more than one row, simply use a PreparedStatement and insert the
values using setXXX.

By inserting more than one row using the select, I meant using the
result of a query (probably without constants like 'value1' or 4523.12).

hth,
Heinz

 
 
 

1. SQL experts: help working around ORA-00934: group function is not allowed here

Considering this statement:

select  substr(tbspc.tablespace_name,1,15) "Tablespace",
        sum(distinct(fil.bytes/1024/1024)) "Total",
        round(sum(free.bytes/1024/1024)) "Free",
        round(sum(free.bytes) / tbspc.next_extent) "# extends",
        substr(file_name, 1, 50) "File Name"
from    dba_tablespaces tbspc,
        dba_free_space free,
        dba_data_files fil
where   fil.file_id = free.file_id
   and  fil.tablespace_name = tbspc.tablespace_name
group   by tbspc.tablespace_name,
        free.file_id,
        file_name,
        round(sum(free.bytes) / tbspc.next_extent);

SQL wants to have the round(sum(free.bytes) / tbspc.next_extent) in the
group by clause bu then, the sum() funcytion is not allowed.

How would you suggest I do this? The help on error 934 says I can use a
sub-select or having, but I don`t know how this can be done here.

Thanks!
--

Syltrem
http://pages.infinit.net/syltrem

2. Scheduled Tasks are not running

3. Access to Oracle (via Visual Basic) - ORA-00934

4. Swapping Column values in SP

5. ORA-04044: procedure, function, package, or type is not allowed here

6. C++ Library based on ODBC

7. am I not allowed to use

8. PostgreSQL/PHP: transactions: how-to abstract out?

9. not a single-group group function ?

10. Microsoft SQL isapi extension - 405 Method Not Allowed - POST queries are not allowed - OpenXML

11. ORA-06571: Function CALC_AVG does not guarantee not to update database

12. ORA-00984: column not allowed here

13. ORA-01723: zero-length columns are not allowed