Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Location
    Phoenix, Arizona
    Posts
    2

    Unanswered: Need help with Oracle Error

    I am trying to run the following code:

    DELETE FROM DIAGNOSIS_DTL A
    WHERE A.CLAIM_REF_NUM =
    (SELECT B.CLAIM_REF_NUM
    FROM CLAIM_SUBMISSION_INFO B
    WHERE A.RECORD_TYPE=B.RECORD_TYPE AND
    A.CLAIM_REF_NUM=B.CLAIM_REF_NUM AND
    B.FILE_NAME='505M20030415')
    AND
    A.RECORD_TYPE=
    (SELECT B.RECORD_TYPE
    FROM CLAIM_SUBMISSION_INFO B
    WHERE A.RECORD_TYPE=B.RECORD_TYPE AND
    A.CLAIM_REF_NUM=B.CLAIM_REF_NUM AND
    B.FILE_NAME='505M20030415');

    And i keep getting the ORA-01427 error message.

    Can any one tell me what is wrong with this statement?

    Thanks,
    John

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Dear John,

    I guess your subquery(any one or both) returns more than 1 rows.

    Try Using this one... ( Don't perform directly DELETE, first perform SELECT operation and if you r sure then and only then perform DELETE operation.)

    DELETE FROM DIAGNOSIS_DTL A
    WHERE A.CLAIM_REF_NUM = ANY (SELECT B.CLAIM_REF_NUM
    FROM CLAIM_SUBMISSION_INFO B
    WHERE A.RECORD_TYPE=B.RECORD_TYPE AND
    A.CLAIM_REF_NUM=B.CLAIM_REF_NUM AND
    B.FILE_NAME='505M20030415')
    AND
    A.RECORD_TYPE= ANY (SELECT B.RECORD_TYPE
    FROM CLAIM_SUBMISSION_INFO B
    WHERE A.RECORD_TYPE=B.RECORD_TYPE AND
    A.CLAIM_REF_NUM=B.CLAIM_REF_NUM AND
    B.FILE_NAME='505M20030415');

    I guess this would help you out.

    Thanks,

    Hings.

    ==============

    Still Learning...

  3. #3
    Join Date
    Apr 2003
    Location
    Phoenix, Arizona
    Posts
    2
    Thanks Hings,

    I found another solution that works even better. In my statement where I have the = signs, I replaced them with the keyword IN. This took care of everything in one step.

    John



    Originally posted by Hings
    Dear John,

    I guess your subquery(any one or both) returns more than 1 rows.

    Try Using this one... ( Don't perform directly DELETE, first perform SELECT operation and if you r sure then and only then perform DELETE operation.)

    DELETE FROM DIAGNOSIS_DTL A
    WHERE A.CLAIM_REF_NUM = ANY (SELECT B.CLAIM_REF_NUM
    FROM CLAIM_SUBMISSION_INFO B
    WHERE A.RECORD_TYPE=B.RECORD_TYPE AND
    A.CLAIM_REF_NUM=B.CLAIM_REF_NUM AND
    B.FILE_NAME='505M20030415')
    AND
    A.RECORD_TYPE= ANY (SELECT B.RECORD_TYPE
    FROM CLAIM_SUBMISSION_INFO B
    WHERE A.RECORD_TYPE=B.RECORD_TYPE AND
    A.CLAIM_REF_NUM=B.CLAIM_REF_NUM AND
    B.FILE_NAME='505M20030415');

    I guess this would help you out.

    Thanks,

    Hings.

    ==============

    Still Learning...

Posting Permissions

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