how to find max(fieldname)

[anagarur]

Hi!

I am tryig to do the foll. query on sql * plus while the table (called CALL) is being loaded.

select max(pk_call) from call;

i have to end the task on windows NT for SQL * Plus. How can i grab this value at any instant.

Is it better to get the max value form the sequnce nstead? The primark key (pk_Call) gets its value from a sequence.

select call_seq.nextval from dual;

will the above work or will i run into the same problem again.

Thanks,
aruna

[marist89]

You can get the current value of the sequence by
select seq.currval from dual;

[sreddy]

MAX returns maximum value of expr. You can use it as an aggregate or analytic function. try using MAX.

SQL> SELECT MAX(sal) "Maximum" FROM emp;

Maximum
----------
5000

[anagarur]

looks like i haven't been clear in my question. I know what MAX does.

I have a situation where a table is being loaded constantly. I am trying to find the last record for that table at a given instant. I am afraid that i am running into a problem when i do that. (ie., select max(pk_call) from CALL;

So, is it better that i do the foll instead?
select call_seq.currval from dual;

Also, is there any limitaion on the maximum no. of rows that a curosr in a pl/sql procedure can have? I ask this 'cos I need to retrieve about 2 million rows at a time and wonder if it will pose a problem. Any ideas please?

thanks,
aruna

[pwoneill]

I don't think you can select the currval of a sequence before you select the nextval once in the session. If you haven't already defined it by selecting (or using) the nextval in the program you're writing you will probably get a ora-08002 when you try to get the currval.

[sreddy]

Iam not sure with your first Q [Count(*) can be used to have an idea howmany rows have been inserted] and I don't know the limit on no of rows for cursor. Alternative is: if its oracle 8i, BULL COLLECT can be used.


The BULK COLLECT clause lets you bulk-bind entire columns of Oracle data (see "Using the BULK COLLECT Clause"). That way, you can fetch all rows from the result set at once. In the following example, you bulk-fetch from a cursor into two collections:

DECLARE
TYPE NumTab IS TABLE OF emp.empno%TYPE;
TYPE NameTab IS TABLE OF emp.ename%TYPE;
nums NumTab;
names NameTab;
CURSOR c1 IS SELECT empno, ename FROM emp WHERE job = 'CLERK';
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO nums, names;
...
CLOSE c1;
END;

[Edited by sreddy on 01-05-2001 at 01:13 PM]

[Mark_Woz]

I am assuming you have programming control over the loading procedure.

If you are inserting into this table using straight SQL, you have a problem. If you are using a PLSQL procedure to insert into the table with a cursor, try the following :

Create a simple table with only one field
eg. CREATE TABLE CALLSTORE (LASTMAXCALL NUMBER );
and insert a row with the value 0 into it.

Every time you insert a record into your load table,
simply update the row in CALLSTORE with the primary key CALL. ie nextval of your sequence.

All you have to do then, is :
select lastmaxcall from callstore.

[pwoneill]

After you insert to the load table, you would want to update with the currval not the nextval right?

[Mark_Woz]

Good point.

[Mark_Woz]

In your PLSQL load procedure, use a variable X. Just before the insert statement :
select CALL_SEQ.NEXTVAL into X;

Change the insert statement to read :

insert into CALL(PK_CALL)
values (X);

then update CALLSTORE with X;

Better ?

[Edited by Mark_Woz on 01-05-2001 at 01:44 PM]