CODEWITHSUNDEEP
sqloracle-database
fras
fras

Reputation: 123

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.

Upvotes: 8

Views: 24562

Answers (3)

Alex
Alex

Reputation:

Why don't you use rownum instead of fetching values from sequence?

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425261

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

Upvotes: 14

KM.
KM.

Reputation: 103579

for IBM Imformix

In a SELECT statement, you cannot specify NEXTVAL or CURRVAL in the following contexts:

  • In the projection list when the DISTINCT keyword is used
  • In the WHERE, GROUP BY, or ORDER BY clauses
  • In a subquery
  • When the UNION operator combines SELECT statements

Upvotes: 1

Related Questions