Re: SEQUENCE Question...

Hi Folks;

    Just wanted to let you all know that Jay's solution offered below works quite well. The solution of creating a function was also offered by cni_at_compusmart.ab.com (no name). The only kink that I had to work out was that the currval and nextval numbers did not seem to be staying in sync properly. I alter the sequence to add the ORDER option and the problem went away.

Matthias Gresz offered the possible solution of including it in a subquery along the lines of:
select * from job_info where job_id = (select job_numbers.currval from dual)

It sounded like a great idea, but as I discovered, these particular psuedo columns cannot be used in a subquery. Or as a part of the where clause for that matter. If you are still reading, here is the pertinent part of the manual. Thanks everyone for your timely assistance.

Where to Use Sequence Values
You can use CURRVAL and NEXTVAL in these places:

• the SELECT list of a SELECT statement that is not contained in a subquery, snapshot, or view
• the SELECT list of a subquery in an INSERT statement
• the VALUES clause of an INSERT statement
• the SET clause of an UPDATE statement

You cannot use CURRVAL and NEXTVAL in these places:

• a subquery in a DELETE, SELECT, or UPDATE statement
• a view's query or snapshot's query
• a SELECT statement with the DISTINCT operator
• a SELECT statement with a GROUP BY or ORDER BY clause
• a SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
• the WHERE clause of a SELECT statement
• DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
• the condition of a CHECK constraint

Jason Jay Weiland wrote:

> Kathy,
>
> You may consider creating a PL/SQL function that will return the
> CurrVal of the sequence that you are attempting to put in the WHERE clause
> of the SELECT statement.
>
> In a text file: c:\temp\new_job.sql
>
> CREATE OR REPLACE FUNCTION f_job_num_currval
> RETURN NUMBER
> IS
> v_job_id NUMBER;
> BEGIN
> SELECT job_numbers.CurrVal
> INTO v_job_id
> FROM dual;
> RETURN v_job_id;
> END;
>
> Then in SQLPlus:
>
> SQL> @c:\temp\new_job
> Function created.
>
> SQL> select * from job_info
> 2 where job_id = f_job_num_currval;
>
> JOB_ID JOB_NAME
> --------- --------------------------------
> 3 Troubleshooting for Kathy
>
> You could also create a procedure that would insert the row and print
> the values it just inserted to the screen, or along those lines, a function
> that would insert the row and return any value you specified.
>
> Jay!!!
>
> P.S> You may want to consider putting "nomail" in the email-address of your
> news-postings' header as well if you want to cut down on spam.
>
> Kathy Graham wrote:
>
>
Received on Tue Oct 06 1998 - 16:15:06 CDT