SELECT DISTINCT "FGFC"."FCMO", "FGFC"."FCYR", "FKITMSTR"."IMDSC", "FGFC"."FCCNQ", "FGFC"."FCBK", "FKITMSTR"."IMSTS", "FKITMSTR"."IMSRTF", "FKITMSTR"."IMPN", "FGFC"."FCCSUM", "FKITMSTR"."IMAWRK", "FKITMSTR"."IMBUY", "FGFC"."FCCO", "FPWRKCTR"."WCSDES", "FPWRKCTR"."WCNO", "FTREQ"."DPQTY", "FKITMSTR"."IMQTOH", "FKITMSTR"."IMMPOC", (SELECT sum("OEPODTL"."ODIQTY")
FROM "BEAUTI1"."SAFILES"."OEPODTL" "OEPODTL"
WHERE "OEPODTL"."ODDTLS"<>'S' and
"FKITMSTR"."IMPN"="OEPODTL"."ODITEM"), (select sum(dpqty) from ftreq
where ftreq.dptno = fkitmstr.impn and
fkitmstr.imco = 1 and
(dpcsmo = 6) and
dpcsyr = 10), (select sum(fccsum) from fgfc
where fkitmstr.impn=fgfc.fcpn and
({FGFC.FCMO} = month({fn CURDATE()})and
{FGFC.FCYR} = 10)), (select sum(fccsum) from fgfc
where fkitmstr.impn=fgfc.fcpn and
({FGFC.FCMO} = month({fn CURDATE()})+1 and
{FGFC.FCYR} = year({fn CURDATE()}))), (select sum(dpqty) from ftreq
where ftreq.dptno = fkitmstr.impn and
fkitmstr.imco = 1 and
(dpcsmo = 7 and
dpcsyr = 10)), (select sum(fccnq) from fgfc
where fgfc.fcpn = fkitmstr.impn and
fkitmstr.imco=fgfc.fcco and
fkitmstr.imco = 1 and
(fcmo = 7 and
fcyr = 10))
FROM {oj (((("BEAUTI1"."KBM400MFG"."FKITMSTR" "FKITMSTR" LEFT OUTER JOIN "BEAUTI1"."KBM400MFG"."FGFC" "FGFC" ON ("FKITMSTR"."IMPN"="FGFC"."FCPN") AND ("FKITMSTR"."IMCO"="FGFC"."FCCO")) LEFT OUTER JOIN "BEAUTI1"."KBM400MFG"."FPROUTNG" "FPROUTNG" ON "FKITMSTR"."IMPN"="FPROUTNG"."RTPN") LEFT OUTER JOIN "BEAUTI1"."SAFILES"."OEPODTL" "OEPODTL" ON "FKITMSTR"."IMPN"="OEPODTL"."ODITEM") LEFT OUTER JOIN "BEAUTI1"."KBM400MFG"."FTREQ" "FTREQ" ON "FKITMSTR"."IMPN"="FTREQ"."DPTNO") INNER JOIN "BEAUTI1"."KBM400MFG"."FPWRKCTR" "FPWRKCTR" ON ("FPROUTNG"."RTCO"="FPWRKCTR"."WCCO") AND ("FPROUTNG"."RTWCNO"="FPWRKCTR"."WCNO")}
WHERE "FGFC"."FCCO"=1 AND ("FKITMSTR"."IMSTS"='A' OR "FKITMSTR"."IMSTS"='C') AND NOT ("FPWRKCTR"."WCSDES"='BACKFLUSH' OR "FPWRKCTR"."WCSDES"='MICRO' OR "FPWRKCTR"."WCSDES"='MICRO INSP') AND "FKITMSTR"."IMMPOC"='M' AND ("FKITMSTR"."IMPN"='12100' OR "FKITMSTR"."IMPN"='14308' OR "FKITMSTR"."IMPN"='15304' OR "FKITMSTR"."IMPN"='153069' OR "FKITMSTR"."IMPN"='15308' OR "FKITMSTR"."IMPN"='15309' OR "FKITMSTR"."IMPN"='16362' OR "FKITMSTR"."IMPN"='16426' OR "FKITMSTR"."IMPN"='16605' OR "FKITMSTR"."IMPN"='16779' OR "FKITMSTR"."IMPN"='18008' OR "FKITMSTR"."IMPN"='46830' OR "FKITMSTR"."IMPN"='46961')
ORDER BY "FKITMSTR"."IMBUY", "FKITMSTR"."IMPN"
I just copied the code from the "show sql"...
This query runs fairly fast, but when I take out the fkitmstr.impn criteria the report hangs up. This sql also shows the code for the sql expressions.