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

Solution 1:

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 ISNULLORDERBY
                c.site_code ASC
        ) q

Solution 2:

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