[John Conklin]

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

[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...

[John Conklin]

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...