SQLTeam.com | Weblogs | Forums

Max value not retrived

Here I am not getting the max value for abt cod for the below SQL

Select p.abtcod

FROM Pxx.MECABSP C
LEFT OUTER JOIN Pxx.MEACBSP A ON C.ACCIPK = A.ACCIPK
LEFT OUTER JOIN Pxx.MEMSRLP M
ON C.MSPIPK=M.MSPIPK
LEFT OUTER JOIN P23QATDT1.MEEYABP P

ON P.ABTCOD=(SELECT MAX(ABTCOD) FROM Pxx.MEEYABP )
AND P.LNKIPK=C.ACCIPK AND P.LKFLCD=130
WHERE C.CARNBR = CAST(C.CARNBR AS DECIMAL(15,0)) AND M.LKFLCD = 100

run this manually,

SELECT MAX(ABTCOD) FROM Pxx.MEEYABP

then stuff the value from above into
where ON P.ABTCOD= XX

to test it manually

You're doing a LEFT JOIN. If a matching row is not found, you will get NULL instead of a value for every column in the LEFT JOIN'd table.