fras85 fras85 - 2 days ago 4
SQL Question

Sequence within SQL Select

I'm having a bit of a problem with using my sequence within a

SELECT
statement.

SELECT
c.cust_name,
c.site,
customer_id_seq.nextval
FROM
customer c
WHERE
c.customer_id IS NULL
ORDER BY
c.site_code ASC
;


Is giving me an error:



  1. 00000 - "sequence number not allowed here"
    *Cause: The specified sequence number (CURRVAL or NEXTVAL) is
    inappropriate
    here in the statement.
    *Action: Remove the sequence number.




It's probably something obvious I'm doing wrong so hopefully this will be an easy answer.

Answer

You cannot use sequences in queries with ORDER BY.

Remove the ORDER BY or put in into a subquery:

SELECT  q.*, customer_id_seq.nextval    
FROM    (
        SELECT  c.cust_name,
                c.site
        FROM    customer c
        WHERE   c.customer_id IS NULL
        ORDER BY
                c.site_code ASC
        ) q
Comments