SQLTeam.com | Weblogs | Forums

Retrieving multiple rows

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;

So my guess is you want only 1 row returned?

You'd have to check the left join tables on the ACCPIK value for multiple records. for P23ADTDT1.MEMSRLP, you'd have to include LKFLCD = 100. Or run this query to see what duplicates are coming thru

select A.ACCIPK, count(1)
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;
group by A.ACCIPK
having count(1) > 1