I used this and works great! Could you or someone help me to figure out how to link the Trip table to multiple tables. Like you said I don't want to add this query to other tables manually as it affects my report's performance.
I have 5 other tables I would like to link using LEFT OUTER JOIN to the Trip Table. I tried a few other ways that I researched online but none work.
FROM "AS400"."MTMODBC"."#MTMTRP" "_MTMTRP",
LEFT OUTER JOIN (
SELECT CHGTRP, CHGFCD, Max(CHGDTE) As Max_CHGDTE, Max(CHGTIM) As Max_CHGTIM
FROM "AS400"."MTMODBC"."#MTMTCHG" "_MTMTCHG"
WHERE CHGFCD= '076'
GROUP BY CHGTRP, CHGFCD
WHERE TINSCD= '0342' and
(TRPFLG Like 'S%' OR
TRPFLG in ('X1','X2','X3')) and
TREASC not in ('53','81') and
Appreciate your help!