Getting multiple rows here, One of my joins is causing issue, can't figure out
Select *
TRIM(AD.PADLN2)||','||TRIM(AD.PADLN1)||','||TRIM (AD.PADCTY)||','||TRIM(AD.PADRGN)||','||TRIM(AD.COUCOD)||','||TRIM (AD.PSLCOD) AS PERADD,
TRIM(PRN.FRSNAM)||','||TRIM(PRN.LSTNAM)||','||TRIM (PRN.MDLINL)||','||TRIM(PRN.NAMMFR) AS PERNAM,
CASE C.CARSTS
WHEN 0 THEN 'ACTIVE'
ELSE 'INACTIVE'
END AS CARSTS,
CASE M.MSPTYP
WHEN 1 THEN 'PRIMARY'
WHEN 2 THEN 'DOMESTIC'
END AS MSPTYP,
CASE C.CARTYP
WHEN 1 THEN 'GOLDSTAR'
END AS CARTYP,
CASE M.MSPSTS
WHEN 0 THEN 'ACTIVE'
ELSE 'INACTIVE'
END AS MSPSTS,
CASE A.CPYNBR
WHEN 1 THEN 'US'
WHEN 4 THEN 'CA'
END AS CPYNBR
FROM P23ADTDT1.MEMSRLP M /* MEMBER RELATIONSHIP FILE */
INNER JOIN P23ADTDT1.MEACBSP A
ON M.ACCIPK = A.ACCIPK /* ACCOUNT FILE */
INNER JOIN P23ADTDT1.MECABSP C
ON M.ACCIPK = C.ACCIPK
AND C.LKFLCD = 100 /* CARD BASIS FILE */
LEFT OUTER JOIN P23ADTDT1.MEPRIDP PR
ON M.LNKIPK = PR.PERIPK /* PERSONAL IDENTIFICATION FILE */
LEFT OUTER JOIN P23ADTDT1.MEPHNBP P
ON M.LNKIPK = P.LNKIPK
AND P.LKFLCD = 100 /* PHONE NUMBERS FILE */
LEFT OUTER JOIN P23ADTDT1.MEEMADP E
ON M.LNKIPK = E.LNKIPK
AND E.LKFLCD = 100 /* E-MAIL FILE */
LEFT OUTER JOIN P23ADTDT1.MEPRNMP PRN
ON M.LNKIPK = PRN.PERIPK /* PERSONAL NAMES FILE */
LEFT OUTER JOIN P23ADTDT1.MEPLADP AD
ON M.LNKIPK = AD.LNKIPK
AND AD.LKFLCD = 100 /* POSTAL ADDRESS FILE */
WHERE M.MSPIPK = CAST(IO_MSPIPK AS DECIMAL (11,0 ) )
AND M.LKFLCD = 100 WITH UR;