CODEWITHSUNDEEP
sqloracle-databaseplsqldistinct
BiscuitCookie
BiscuitCookie

Reputation: 709

ORACLE APEX SQL how to count distinct fields

I am trying to insert data into a table where an id from a table isn't distinct however, I get the following error message:

PL/SQL: ORA-02287: sequence number not allowed here.

What is the best way to get around this?

I have copied the code below;

 INSERT INTO data_quality
    SELECT  QLTY_SEQ.nextval, userid, 'Duplicate ID'
    FROM user
    HAVING COUNT(DISTINCT(userid)) = 1;

Thank You in Advance.

Upvotes: 0

Views: 308

Answers (1)

Aleksej
Aleksej

Reputation: 22949

You have more than one issue here: once you fixed the sequence, you will have an error due to the fact that you are applying an HAVING without a GROUP BY. You probably need :

INSERT INTO data_quality
    SELECT  QLTY_SEQ.nextval, userid, 'Duplicate ID'
    FROM
    (
      SELECT userid 
      FROM user
      GROUP BY userid
      HAVING COUNT(DISTINCT(userid)) = 1
    )

Upvotes: 1

Related Questions