Hi there People, Please help!!
having a problem I'll TRY and explain it:-
Some background.
Its a purchase order report with a Header (POM0) table and a Detail (POI0) table.
We have obviously a relationship between the two tables ORDRNBR.
That works fine also with other tables included for ShipTo, Buyer data etc.
The problem is we have another table with Vendor Part detail in (VPM0) and unfortunetly we need a field from both the Header (VENDID) and Detail (PARTNBR) table to reference this.
On occasions the record inside the VPM0 table will not exist so when that happens I need to include our Part Number field into the report instead.
Obviously I can't get this thing to work.
If I include Left Outer joins on just the links to the VPM0 table my PC just hangs. (taking up 40% processing of the AS400)
If I don't change any of the join options I don't get all the records returned.
Using IBM client access and the data is held on the AS400.
I'll include all the SQL
==
SELECT
POM0."ORDRNBR", POM0."RLSECNT", POM0."VENDID", POM0."ACKNSW", POM0."SPCINST", POM0."INITEND", POM0."CODCURAI",
POI0."LNNBRC", POI0."PARTNBR", POI0."PRTDESC", POI0."SCHDATE", POI0."QTYORDR", POI0."AMTUNIT",
VNA0."VENNAME", VNA0."ADDR1", VNA0."CITY", VNA0."STATE", VNA0."POSTAL", VNA0."LOCCODE",
ULC0."NAMEADDR", ULC0."ADDR04", ULC0."LOCNCODE", ULC0."PHON",
VPM0."VENDPART",
CEN0_1."NAMEADDR", CEN0_1."ADDR01", CEN0_1."CITY", CEN0_1."STTE", CEN0_1."PSTL",
CEN0."NAMEADDR", CEN0."ADDR01", CEN0."ADDR03", CEN0."ADDR04", CEN0."CITY", CEN0."STTE", CEN0."PSTL"
FROM
"NWI"."M30DINST"."POM0" POM0,
"NWI"."M30DINST"."POI0" POI0,
"NWI"."M30DINST"."VNA0" VNA0,
"NWI"."M30DINST"."ULC0" ULC0,
"NWI"."M30DINST"."VPM0" VPM0,
"NWI"."M30DINST"."CEN0" CEN0_1,
"NWI"."M30DINST"."CEN0" CEN0
WHERE
POM0."ORDRNBR" = POI0."ORDRNBR" AND
POM0."VENDID" = VNA0."VENID" AND
POM0."IDBUYRP" = ULC0."USERID" AND
POM0."VENDID" = VPM0."VENDID" AND
POI0."BILLCNTR" = CEN0_1."CNTR" AND
POI0."SHPTCNTR" = CEN0."CNTR"
ORDER BY
POM0."ORDRNBR" ASC
Well, I've been working on this for two long and I have a headache. Please help!
Andy