Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2008
    Location
    Ireland
    Posts
    11

    Unanswered: ORA-30926 - Problem with Merge

    Hi,

    I recieved this error and after looking it up it appears I have a problem with a non-discriminate WHERE clause:
    Cause: A stable set of rows could not be got because of large dml activity or a non-deterministic where clause.
    Action: Remove any non-deterministic where clauses and reissue the dml.


    I have looked over the code and as far as I can tell everything looks ok. I have run this code before and not gotten this statement so Im very confused as to why its appearring now.
    Can anyone see what I'm missing - or know anymore about this error?
    All help and advice is greatly appreciated.

    MERGE INTO TBL_MDA_CREATE_ATTR_LKP A USING
    (SELECT distinct txn_sequence,
    lot_id,
    CASE WHEN mda_create_handle IS NOT NULL THEN lead(txn_dt) OVER (PARTITION BY lot_id ORDER BY txn_dt )
    ELSE NULL
    END next_txn_dt
    FROM TBL_MDA_CREATE_ATTR_LKP ) b
    ON (A.txn_sequence = b.txn_sequence )
    WHEN MATCHED THEN
    UPDATE SET A.next_txn_dt=b.next_txn_dt;

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down Syntax error

    ANALYTICAL FUNCTIONS are not allowed as return expresions from a CASE statement, all of the return exprs must either have the same datatype (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or must all have a numeric datatype:
    Code:
    CASE WHEN mda_create_handle IS NOT NULL THEN lead(txn_dt) OVER (PARTITION BY lot_id ORDER BY txn_dt ) 
    ELSE NULL 
    END next_txn_dt
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jul 2008
    Location
    Ireland
    Posts
    11

    CAST results

    Thanks for your reply.

    To avoid this I CAST the analytical function asa VARCHAR2 and get the same result and error.

    CASE WHEN mda_create_handle IS NOT NULL
    THEN
    CAST(lead(txn_dt) OVER (PARTITION BY lot_id ORDER BY txn_dt ) AS VARCHAR2(16))
    ELSE NULL
    END next_txn_dt

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down a NO-NO

    Maybe I was not clear enough: YOU CANNOT USE AN ANALYTICAL FUNCTION INSIDE A CASE STATEMENT.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •