SELECT
AlternateName.firstForename,
AlternateName.surname,
Person.dateOfBirth,
Person.gender,
Person.personBirthName,
Person.motherBirthSurname,
Person.primaryAlternateID,
Person.concernRoleID,
ConcernRole.sensitivity,
city.elementValue,
addressLine1.elementValue
INTO
:firstForename,
:surname,
:dateOfBirth,
:gender,
:personBirthName,
:motherBirthSurname,
:primaryAlternateID,
:concernRoleID,
:sensitivity,
:city,
:addressLine1
FROM
Person,
ConcernRole,
AlternateName,
AddressElement city,
AddressElement addressLine1
WHERE
(
:searchByFirstForename = '0'
OR AlternateName.upperFirstForename like :firstForename
)
AND
(
:searchByPhoneticEncode = '0'
OR AlternateName.phoneticEncoding = :phoneticEncode
)
AND ConcernRole.concernRoleID = Person.concernRoleID
AND AlternateName.concernRoleID = Person.concernRoleID
AND addressLine1.addressID = ConcernRole.primaryAddressID
AND addressLine1.elementType = :addressLine1Type
AND city.addressID = ConcernRole.primaryAddressID
AND city.elementType = :cityType
AND
(
:searchByAddressLine1 = '0'
OR addressLine1.upperElementValue like :addressLine1
)
AND
(
:searchByCity = '0'
OR city.upperElementValue like :city
)
AND
(
:searchByGender = '0'
OR Person.gender = :gender
)
AND
(
:searchByDateOfBirth = '0'
OR Person.dateOfBirth = :dateOfBirth
)
AND
(
:searchByPersonBirthName = '0'
OR Person.upperPersonBirthName like :personBirthname
)
AND
(
:searchByMotherBirthName = '0'
OR Person.upperMotherBirthSurname like :motherBirthSurname
)
|