SELECT to_char(PD.DATE_OF_INFO, 'MM/DD/RRRR') AS DATE_OF_INFO, UPPER(inst.db_schema) ||'_'||PD.PHYS_DESC_ID as ID, P.MASTER_PERSON_ID, PD.PHYS_DESC_ID, inst.default_state AS state, inst.county_name AS county_name, UPPER(inst.db_schema) as county, CASE WHEN LENGTH(HEIGHT) = 1 THEN HEIGHT||'00' WHEN LENGTH(HEIGHT) = 2 THEN '0'||HEIGHT WHEN LENGTH(HEIGHT) = 3 THEN HEIGHT END AS HEIGHT, PD.WEIGHT as WEIGHT, PD.AGE as AGE, PD.GLASSES AS GLASSES, PD.HAIR_CD_HAIR_CODE AS HAIR, PD.EYE_CD_EYE_CODE AS EYE, PD.HAIR_LNGTH_HAIR_LENGTH_CODE AS HAIRLENGTH, PD.BLD_CD_BUILD_CODE AS EYEBUILD, PD.SKN_CD_SKIN_CODE AS SKINCOLOR, PD.FACE_HR_FACIAL_HAIR AS FACIAL_HAIR, PD.HAIR_STYLE_CODE AS HAIR_STYLE, MP.SEX_CODE as SEX, MP.ETHNICITY_CODE as ETHNICITY, MP.RACE_CODE as RACE, ( (CASE WHEN PD.HEIGHT IS NOT NULL THEN 'Height: ' || CASE WHEN LENGTH(HEIGHT) = 1 THEN HEIGHT||'00' WHEN LENGTH(HEIGHT) = 2 THEN '0'||HEIGHT WHEN LENGTH(HEIGHT) = 3 THEN SUBSTR(HEIGHT,1,1) || ''' '|| SUBSTR(HEIGHT,2,2)|| '"' END END ) || ' ' || (CASE WHEN PD.WEIGHT IS NOT NULL THEN 'Weight: ' || PD.WEIGHT || 'lbs' ELSE '' END) || ' ' || (CASE WHEN PD.AGE IS NOT NULL THEN 'Age: ' || PD.AGE || ' years old' ELSE '' END) || ' ' || (CASE WHEN SEXC.DESCRIPTION IS NOT NULL THEN 'Sex: ' || SEXC.DESCRIPTION ELSE '' END) || ' ' || (CASE WHEN RACC.DESCRIPTION IS NOT NULL THEN 'Race: ' || RACC.DESCRIPTION ELSE '' END) || ' ' || (CASE WHEN ETHC.DESCRIPTION IS NOT NULL THEN 'Ethnicity: ' || ETHC.DESCRIPTION ELSE '' END) || ' ' || (CASE WHEN SC.DESCRIPTION IS NOT NULL THEN 'Skin Color: ' || SC.DESCRIPTION ELSE '' END) || ' ' || (CASE WHEN HC.DESCRIPTION IS NOT NULL THEN 'Hair: ' || HC.DESCRIPTION ELSE '' END) || ' ' || (CASE WHEN HSC.DESCRIPTION IS NOT NULL THEN 'Hair Style: ' || HSC.DESCRIPTION ELSE '' END) || ' ' || (CASE WHEN HAC.DESCRIPTION IS NOT NULL THEN 'Hair Length: ' || HAC.DESCRIPTION ELSE '' END) || ' ' || (CASE WHEN FC.DESCRIPTION IS NOT NULL THEN 'Facial Hair: ' || FC.DESCRIPTION ELSE '' END) || ' ' || (CASE WHEN EC.DESCRIPTION IS NOT NULL THEN 'Eye Color: ' || EC.DESCRIPTION ELSE '' END) || ' ' || (CASE WHEN EBC.DESCRIPTION IS NOT NULL THEN 'Build: ' || EBC.DESCRIPTION ELSE '' END) || ' ' || 'Glasses: ' || PD.GLASSES ), PI.IMA_IMAGE_ID, PI.MC1_MUGSHOT_CODE, TO_CHAR(PI.IMAGE_DATE,'RRRR-MM-DD"T"HH24:MI:SS"Z"'), JOIN (CURSOR( SELECT PI2.IMA_IMAGE_ID FROM MASTER_PEOPLE MP2 INNER JOIN PEOPLE P2 ON MP2.MASTER_PERSON_ID = P2.MASTER_PERSON_ID INNER JOIN PERSON_IMAGES PI2 ON P2.PERSON_ID = PI2.PER_PERSON_ID WHERE MP2.MASTER_PERSON_Id = MP.MASTER_PERSON_ID AND PI2.IMA_IMAGE_ID != NVL(PI.IMA_IMAGE_ID,-1) AND PI2.PHY_PHYS_DESC_ID = PD.PHYS_DESC_ID AND PI2.MC1_MUGSHOT_CODE IN (SELECT ECR.PARENT_CODE FROM EJS_CODES_RELATIONS ECR WHERE ECR.PARENT_CODE_TYPE = 'MUGSHOT_CODES' AND ECR.CHILD_CODE_TYPE = 'PHOTO_LINEUP_DISPLAYABLE') ) ,','), (CASE WHEN GP.GANG_ID IS NOT NULL THEN 'Y' ELSE 'N' END) FROM INSTALL INST, PHYSICAL_DESCRIPTIONS PD INNER JOIN PEOPLE P ON PD.PER_PERSON_ID = P.PERSON_ID INNER JOIN MASTER_PEOPLE MP ON P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID INNER JOIN PERSON_IMAGES PI ON PD.PHYS_DESC_ID = PI.PHY_PHYS_DESC_ID LEFT OUTER JOIN EJS_CODES HC ON PD.HAIR_CD_HAIR_CODE = HC.CODE AND PD.HAIR_CD_HAIR_CODE_TYPE = HC.CODE_TYPE LEFT OUTER JOIN EJS_CODES EC ON PD.EYE_CD_EYE_CODE = EC.CODE AND PD.EYE_CD_EYE_CODE_TYPE = EC.CODE_TYPE LEFT OUTER JOIN EJS_CODES HAC ON PD.HAIR_LNGTH_HAIR_LENGTH_CODE = HAC.CODE AND PD.HAIR_LNGTH_HAIR_LENGTH_CO_TYPE = EC.CODE_TYPE LEFT OUTER JOIN EJS_CODES EBC ON PD.BLD_CD_BUILD_CODE = EBC.CODE AND PD.BLD_CD_BUILD_CODE_TYPE = EBC.CODE_TYPE LEFT OUTER JOIN EJS_CODES SC ON PD.SKN_CD_SKIN_CODE = SC.CODE AND PD.SKN_CD_SKIN_CODE_TYPE = SC.CODE_TYPE LEFT OUTER JOIN EJS_CODES FC ON PD.FACE_HR_FACIAL_HAIR = FC.CODE AND PD.FACE_HR_FACIAL_HAIR_TYPE = FC.CODE_TYPE LEFT OUTER JOIN EJS_CODES HSC ON PD.HAIR_STYLE_CODE = HSC.CODE AND PD.HAIR_STYLE_TYPE = HSC.CODE_TYPE LEFT OUTER JOIN EJS_CODES SEXC ON MP.SEX_CODE = SEXC.CODE AND MP.SEX_CODE_TYPE = SEXC.CODE_TYPE LEFT OUTER JOIN EJS_CODES ETHC ON MP.ETHNICITY_CODE = ETHC.CODE AND MP.ETHNICITY_CODE_TYPE = ETHC.CODE_TYPE LEFT OUTER JOIN EJS_CODES RACC ON MP.RACE_CODE = RACC.CODE AND MP.RACE_CODE_TYPE = RACC.CODE_TYPE LEFT OUTER JOIN GANG_PEOPLE GP ON GP.PERSON_ID = P.PERSON_ID