Results 1 to 8 of 8
  1. #1
    Join Date
    May 2008
    Posts
    4

    Unanswered: Oracle SQL Error (help)

    Folks, I have an error with using the oracle sequence in the function. I would like to get the greatest value by comparing the sequence value wit the max value in the oracle table.

    Select greatest((select max(id) from Table),(select MySequence.currval from dual)) from dual.

    The have the error as "ORA-02287: Sequence number not allowed here".

    Looks like I can't use the sequence inside the aggregate function.

    Will somebody please aid me for an alternative?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by srinku13
    Will somebody please aid me for an alternative?
    we'll try

    what the heck are you trying to do???

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2008
    Posts
    4
    SELECT GREATEST(1,2) FROM DUAL ---> WORKS GOOD

    The value 2 is from the sequence call (MySequence.currval), and is hitting me with an error. Not sure how to handle the sequence call inside the aggregate functions.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what i meant was, you seem to be trying to get the MAX(id) in the table, or the next value of some serial, whichever is greater -- what in the world would you want to do that for?

    i r puzzled
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2008
    Posts
    4
    Assume, I have two interfaces A & B using the same table. Interface A using the seq.nextval to get the ID and insert, and interface B using max(ID) +1. Ofcourse both interacting with the same table. To be consistent, I would like to change interface A to use the above query for a check and balance.

    Thanks for your replies.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd suggest you to abandon "MAX + 1" and never use it in multi-user environment. Switch your code to use of a SEQUENCE and never worry about the problem you are trying to deal with now.

    However, if you insist on this solution, it seems that you'll have to use PL/SQL code (a function might be fine), such as
    Code:
    CREATE OR REPLACE FUNCTION Fun_Max RETURN NUMBER IS
      l_seq NUMBER;
      l_deptno NUMBER;
    BEGIN
      SELECT my_seq.NEXTVAL INTO l_seq FROM dual;
      SELECT MAX(deptno) INTO l_deptno FROM DEPT;
    		
      RETURN (GREATEST(l_seq, l_deptno));
    END;
    Use it as
    Code:
    SQL> SELECT fun_max FROM dual;
    
       FUN_MAX
    ----------
            94
    
    SQL>

  7. #7
    Join Date
    May 2008
    Posts
    4
    LittleFoot, Thanks alot. That was a good one. I will see, if I can implement this funcion call instead of just a sql call from my client(s). Gracias.

  8. #8
    Join Date
    May 2008
    Posts
    2
    You will have to go Littlefoot's way. You cannot call a sequence from within a select!
    Last edited by r937; 05-12-08 at 17:56.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •