I have a work table that gets populated with data from a client feed. Their data (the client feed), does not contain the field we use internally to uniquely identify a person(CorpID). This data is for insurance info and the identifying numbers that the clients are familiar with is a 2 field identifier (SubscriberID-MemberNumber). Basically the SubscriberID identifies a family and the MemberNumber identifies the individual within the family.
Ex:
SubscriberID MemberNumber
Me 123456789 00
Wife 123456789 01
Child1 123456789 02
Child2 123456789 03
Unfortunately the client feed does not contain the MemberNumber value but does contain name, dob, and gender. I want to update the CorpID field in the work table by linking to our members table on the SubscriberID, first name, dob, and gender fields. I get this error:
"The result of a scalar fullselect, Select Into statement, or Values Into statement is more than one row. SQLSTATE 21000"
Here is my SQL:
UPDATE WORK_TBL SET (CorpID, MemberNumber) = (Select MEMBER.CorpID, MEMBER.MemberNumber From MEMBER INNER JOIN WORK_TBL On WORK_TBL.PatientDOB = MEMBER.MBR_BRTH_DATE AND WORK_TBL.PatientSex = MEMBER.MBR_SEX_CODE AND WORK_TBL.SubscriberID = MEMBER.MBR_SUB_ID And WORK_TBL.PatientFirstName = MEMBER.MBR_FRST_NAME)
This is DB2 v7.1 on OS/390.
Ex:
SubscriberID MemberNumber
Me 123456789 00
Wife 123456789 01
Child1 123456789 02
Child2 123456789 03
Unfortunately the client feed does not contain the MemberNumber value but does contain name, dob, and gender. I want to update the CorpID field in the work table by linking to our members table on the SubscriberID, first name, dob, and gender fields. I get this error:
"The result of a scalar fullselect, Select Into statement, or Values Into statement is more than one row. SQLSTATE 21000"
Here is my SQL:
UPDATE WORK_TBL SET (CorpID, MemberNumber) = (Select MEMBER.CorpID, MEMBER.MemberNumber From MEMBER INNER JOIN WORK_TBL On WORK_TBL.PatientDOB = MEMBER.MBR_BRTH_DATE AND WORK_TBL.PatientSex = MEMBER.MBR_SEX_CODE AND WORK_TBL.SubscriberID = MEMBER.MBR_SUB_ID And WORK_TBL.PatientFirstName = MEMBER.MBR_FRST_NAME)
This is DB2 v7.1 on OS/390.