The below SQL retrieves multiple rows which is incorrect, not sure my LEFT outer join is the issue. Any comments are much appreciated.
SELECT
A.ACCIPK,
CASE A.ACCTYP
WHEN 1 THEN 'GOLDSTAR'
WHEN 2 THEN 'BUSINESS'
WHEN 3 THEN 'EMPLOYEE'
WHEN 4 THEN 'GOLDSTARCOMP'
WHEN 5 THEN 'INTERNAL'
WHEN 6 THEN 'ANCILLARY'
END AS ACCTYP,
A.ACEFDT,
A.ACXPDT,
CASE A.ACCSTS
WHEN 0 THEN 'ACTIVE'
WHEN 999 THEN 'INACTIVE'
END AS ACCSTS,
CASE A.CPYNBR
WHEN 1 THEN 'US'
WHEN 4 THEN 'CA'
END AS CPYNBR,
B.CAWCOD AS CAWCOD,
M.MSPIPK,
M.ERLFCY,
M.ERLDAT,
M.LNKIPK AS PERIPK,
CASE M.MSPSTS
WHEN 0 THEN 'ACTIVE'
ELSE 'INACTIVE'
END AS MSPSTS,
CASE M.MSPTYP WHEN 1 THEN 'PRIMARY'
WHEN 2 THEN 'DOMESTIC'
WHEN 3 THEN 'ADD ON'
WHEN 5 THEN 'SUPPLY CARD'
WHEN 6 THEN 'COMPANY CARD'
WHEN 7 THEN 'WAREHOUSE ANCILLARY' END AS MSPTYP,
CONCAT(M.MSPIPK,(CONCAT('-',M.LNKIPK))) AS MPID,
CONCAT(A.ACCIPK,(CONCAT('-',M.MSPIPK))) AS AMID
FROM P23ADTDT1.MEACBSP A
LEFT OUTER JOIN P23ADTDT1.MEMKCWP B ON A.ACCIPK = B.LNKIPK
LEFT OUTER JOIN P23ADTDT1.MEMSRLP M ON A.ACCIPK = M.ACCIPK AND M.LKFLCD = 100
WHERE A.ACCIPK = CAST(IO_ACCIPK AS DECIMAL(11,0)) WITH UR;