SELECT
AlternateName.firstForename,
AlternateName.surname,
ProspectPerson.dateOfBirth,
ProspectPerson.gender,
ProspectPerson.personBirthName,
ProspectPerson.motherBirthSurname,
ProspectPerson.primaryAlternateID,
ProspectPerson.concernRoleID,
ProspectPerson.personConcernRoleID,
ConcernRole.sensitivity,
city.elementValue,
addressLine1.elementValue,
ConcernRoleDuplicate.originalConcernRoleID
INTO
:firstForename,
:surname,
:dateOfBirth,
:gender,
:personBirthName,
:motherBirthSurname,
:primaryAlternateID,
:concernRoleID,
:personConcernRoleID,
:sensitivity,
:city,
:addressLine1,
:originalConcernRoleID
FROM
(ProspectPerson
LEFT OUTER JOIN
ConcernRoleDuplicate
ON ConcernRoleDuplicate.duplicateConcernRoleID =ProspectPerson.concernRoleID),
ConcernRole,
AlternateName,
AddressElement city,
AddressElement addressLine1
WHERE ConcernRole.concernRoleID IN
(
SELECT
concernRoleID
FROM
concernRoleAlternateID
WHERE concernRoleAlternateID.alternateID = :primaryAlternateID
)
AND ProspectPerson.concernRoleID = ConcernRole.concernRoleID
AND ProspectPerson.primaryAlternateNameID = AlternateName.alternateNameID
AND
(
city.addressID = ConcernRole.primaryAddressID
AND city.elementType = :cityType
)
AND
(
addressLine1.addressID = ConcernRole.primaryAddressID
AND addressLine1.elementType = :addressLine1Type
)
|