Don't use SQL in advance situations, What does this do:
join ACTPHY01 on RPLWNB = B1LWNB WHERE B1IAST ='A' and ((SELECT SUM(C.B1MDNB+C.B1MENB+C.B1M7NR+C.B1M8NR+C.B1M9NR) FROM ACTPHY01 C WHERE C.B1LKNB=RPI8NS and C.B1HXCD=RPMBXU) > 0 OR EXISTS (SELECT 1 FROM CSHPHY01 A
SELECT RPI8NS as STM_A, RPMBXU as STM_B, RPLWNB as Service_Account, RPMDXU as Customer, RPBUDF as Bill_Date, RPMCXU as Bill_Cycle, RPMLXU as Act_Type, RPMEXU as Address_1, RPMFXU as Address_2, RPPNXU as Address_3, RPPOXU as Address_4, RPPPXU as Address_5, RPMGXU as City, RPMHXU as State, RPMIXU as Zip_Code, K8FENN as Overdue_Amount, RPJANS as Ovr_Chrg, RPJBNS as Regulated_Amt, RPJCNS As Un_Reg_Amt, RPMMXU as NPA, RPMNXU as Exe, RPMOXU as Number, substr(RPBVDF +19000000, 5, 2) ||'-'|| substr(RPBVDF +19000000, 7, 2) ||'-'|| substr(RPBVDF +19000000, 1, 4) as Due_Date, RPBWDF as DX_Date, max(HQTQDT) as PAY_Dte FROM LTRPHY01 left outer join PSUPHY01 on RPLWNB = HQLWNB and HQFRSS ='P' and HQZ5ST ='P' left outer join pldphy01 on RPBUDF=k8tidt and RPlwnb=k8lwnb and k8fmnn=1 left outer join ACTPHY01 on RPLWNB = B1LWNB WHERE B1IAST ='A' and ((SELECT SUM(C.B1MDNB+C.B1MENB+C.B1M7NR+C.B1M8NR+C.B1M9NR) FROM ACTPHY01 C WHERE C.B1LKNB=RPI8NS and C.B1HXCD=RPMBXU) > 0 OR EXISTS (SELECT 1 FROM CSHPHY01 A WHERE RPI8NS= a.S3LKNB AND RPMBXU= a.S3HXCD AND A.S3HJCD IN ('E','F') AND A.S3UXNP in (Select max(b.S3UXNP) from CSHPHY01 B WHERE A.S3LWNB=B.S3LWNB))) GROUP BY RPI8NS, RPMBXU, RPLWNB, RPMDXU, RPBUDF, RPMCXU, RPMLXU, RPMEXU, RPMFXU, RPPNXU, RPPOXU, RPPPXU, RPMGXU, RPMHXU, RPMIXU, K8FENN, RPJANS, RPJBNS, RPJCNS, RPMMXU, RPMNXU, RPMOXU, RPBVDF, RPBWDF ORDER BY RPMIXU
Answer by Michael Haren ·
It's just an insane join predicate. That is, you have a query joining in the table
ACTPHY01 exclusively when that ridiculous predicate is satisfied.
I hope that the schema and queries you are working with were automatically generated because they are definitely not hand baked with love.