The report worked fine before I added the base cost field (added POVAGRMTHD and POVAGRMTLN tables). I believe it has something to do with the linking. I've tried different types of joins to no avail. Any ideas on what may be linked incorrectly?
This Crystal report queries the following tables:
BUYER, ICMANFCODE, L_HMAG, MAINVDTL, MAINVMSG, POLINE, POPCRUF, POPIVUF, POVAGRMTHD, POVAGRMTLN, PURCHORDER
Here is the SQL from the original report that works fine:
SELECT DISTINCT "MAINVMSG"."COMPANY", "MAINVMSG"."VENDOR", "MAINVMSG"."PO_NUMBER", "MAINVMSG"."PO_RELEASE", "MAINVMSG"."PO_CODE", "MAINVMSG"."PO_LINE_NBR", "MAINVMSG"."REASON_CODE", "MAINVMSG"."R_STATUS", "MAINVMSG"."OPERATOR", "MAINVMSG"."INVOICE", "MAINVMSG"."VEN_ITEM", "MAINVMSG"."CREATE_DATE", "MAINVMSG"."RESPOND_DATE", "PURCHORDER"."PO_DATE", "PURCHORDER"."BUYER_CODE", "POLINE"."ITEM", "POLINE"."ENT_UNIT_CST", "MAINVDTL"."ENT_UNIT_CST", "POLINE"."LINE_NBR", "POLINE"."MANUF_CODE", "POLINE"."MANUF_NBR", "POPIVUF"."PIV_USR_FLD_01", "POPIVUF"."PIV_USR_FLD_02", "POPIVUF"."PIV_USR_FLD_03", "POLINE"."QUANTITY", "POLINE"."REC_QTY", "MAINVDTL"."MATCHED_QTY", "L_HMAG"."OBJECT", "POLINE"."ITEM_TYPE", "MAINVMSG"."BUYER_CODE", "MAINVMSG"."AUTH_CODE", "POLINE"."BUYER_CODE", "BUYER"."R_NAME", "MAINVDTL"."UPDATE_DATE", "POPCRUF"."PCR_USR_FLD_01" FROM (("LAWPROD"."PURCHORDER" "PURCHORDER" INNER JOIN "LAWPROD"."POLINE" "POLINE" ON ((("PURCHORDER"."COMPANY"="POLINE"."COMPANY") AND ("PURCHORDER"."PO_NUMBER"="POLINE"."PO_NUMBER")) AND ("PURCHORDER"."PO_CODE"="POLINE"."PO_CODE")) AND ("PURCHORDER"."PO_RELEASE"="POLINE"."PO_RELEASE")) RIGHT OUTER JOIN ("LAWPROD"."L_HMAG" "L_HMAG" RIGHT OUTER JOIN ("LAWPROD"."POPCRUF" "POPCRUF" RIGHT OUTER JOIN (("LAWPROD"."MAINVDTL" "MAINVDTL" LEFT OUTER JOIN "LAWPROD"."MAINVMSG" "MAINVMSG" ON (((((((("MAINVDTL"."COMPANY"="MAINVMSG"."COMPANY") AND ("MAINVDTL"."VENDOR"="MAINVMSG"."VENDOR")) AND ("MAINVDTL"."INVOICE"="MAINVMSG"."INVOICE")) AND ("MAINVDTL"."PO_NUMBER"="MAINVMSG"."PO_NUMBER")) AND ("MAINVDTL"."PO_RELEASE"="MAINVMSG"."PO_RELEASE")) AND ("MAINVDTL"."PO_CODE"="MAINVMSG"."PO_CODE")) AND ("MAINVDTL"."LINE_NBR"="MAINVMSG"."PO_LINE_NBR")) AND ("MAINVDTL"."ITEM"="MAINVMSG"."ITEM")) AND ("MAINVDTL"."SUFFIX"="MAINVMSG"."SUFFIX")) LEFT OUTER JOIN "LAWPROD"."POPIVUF" "POPIVUF" ON (("MAINVDTL"."ITEM"="POPIVUF"."ITEM") AND ("MAINVDTL"."VENDOR"="POPIVUF"."VENDOR")) AND ("MAINVDTL"."VEN_ITEM"="POPIVUF"."VEN_ITEM")) ON ((("POPCRUF"."COMPANY"="MAINVMSG"."COMPANY") AND ("POPCRUF"."PO_CODE"="MAINVMSG"."PO_CODE")) AND ("POPCRUF"."PO_NUMBER"="MAINVMSG"."PO_NUMBER")) AND ("POPCRUF"."PO_RELEASE"="MAINVMSG"."PO_RELEASE")) ON "L_HMAG"."L_INDEX"="MAINVMSG"."L_INDEX") ON (((("POLINE"."COMPANY"="MAINVMSG"."COMPANY") AND ("POLINE"."PO_NUMBER"="MAINVMSG"."PO_NUMBER")) AND ("POLINE"."PO_RELEASE"="MAINVMSG"."PO_RELEASE")) AND ("POLINE"."PO_CODE"="MAINVMSG"."PO_CODE")) AND ("POLINE"."LINE_NBR"="MAINVMSG"."PO_LINE_NBR")) INNER JOIN "LAWPROD"."BUYER" "BUYER" ON ("MAINVMSG"."PROCURE_GROUP"="BUYER"."PROCURE_GROUP") AND ("MAINVMSG"."BUYER_CODE"="BUYER"."BUYER_CODE") WHERE ("MAINVMSG"."CREATE_DATE">={ts '2016-01-01 00:00:00'} AND "MAINVMSG"."CREATE_DATE"<{ts '2016-08-02 00:00:00'}) AND "MAINVMSG"."BUYER_CODE"='JAS' ORDER BY "POLINE"."BUYER_CODE"
Here is the new SQL after I added the Vendor agreement tables:
SELECT DISTINCT "MAINVMSG"."COMPANY", "MAINVMSG"."VENDOR", "MAINVMSG"."PO_NUMBER", "MAINVMSG"."PO_RELEASE", "MAINVMSG"."PO_CODE", "MAINVMSG"."PO_LINE_NBR", "MAINVMSG"."REASON_CODE", "MAINVMSG"."R_STATUS", "MAINVMSG"."OPERATOR", "MAINVMSG"."INVOICE", "MAINVMSG"."VEN_ITEM", "MAINVMSG"."CREATE_DATE", "MAINVMSG"."RESPOND_DATE", "PURCHORDER"."PO_DATE", "PURCHORDER"."BUYER_CODE", "POLINE"."ITEM", "POLINE"."ENT_UNIT_CST", "MAINVDTL"."ENT_UNIT_CST", "POLINE"."LINE_NBR", "POLINE"."MANUF_CODE", "POLINE"."MANUF_NBR", "POPIVUF"."PIV_USR_FLD_01", "POPIVUF"."PIV_USR_FLD_02", "POPIVUF"."PIV_USR_FLD_03", "POLINE"."QUANTITY", "POLINE"."REC_QTY", "MAINVDTL"."MATCHED_QTY", "L_HMAG"."OBJECT", "POLINE"."ITEM_TYPE", "MAINVMSG"."BUYER_CODE", "MAINVMSG"."AUTH_CODE", "POLINE"."BUYER_CODE", "BUYER"."R_NAME", "MAINVDTL"."UPDATE_DATE", "POPCRUF"."PCR_USR_FLD_01", "POVAGRMTLN"."HOLD_FLAG", "POVAGRMTLN"."BASE_COST" FROM (("LAWPROD"."PURCHORDER" "PURCHORDER" INNER JOIN ("LAWPROD"."POVAGRMTLN" "POVAGRMTLN" LEFT OUTER JOIN "LAWPROD"."POLINE" "POLINE" ON "POVAGRMTLN"."ITEM"="POLINE"."ITEM") ON ((("PURCHORDER"."COMPANY"="POLINE"."COMPANY") AND ("PURCHORDER"."PO_NUMBER"="POLINE"."PO_NUMBER")) AND ("PURCHORDER"."PO_CODE"="POLINE"."PO_CODE")) AND ("PURCHORDER"."PO_RELEASE"="POLINE"."PO_RELEASE")) RIGHT OUTER JOIN ("LAWPROD"."L_HMAG" "L_HMAG" RIGHT OUTER JOIN ("LAWPROD"."POPCRUF" "POPCRUF" RIGHT OUTER JOIN (("LAWPROD"."MAINVDTL" "MAINVDTL" LEFT OUTER JOIN "LAWPROD"."MAINVMSG" "MAINVMSG" ON (((((((("MAINVDTL"."COMPANY"="MAINVMSG"."COMPANY") AND ("MAINVDTL"."VENDOR"="MAINVMSG"."VENDOR")) AND ("MAINVDTL"."INVOICE"="MAINVMSG"."INVOICE")) AND ("MAINVDTL"."PO_NUMBER"="MAINVMSG"."PO_NUMBER")) AND ("MAINVDTL"."PO_RELEASE"="MAINVMSG"."PO_RELEASE")) AND ("MAINVDTL"."PO_CODE"="MAINVMSG"."PO_CODE")) AND ("MAINVDTL"."LINE_NBR"="MAINVMSG"."PO_LINE_NBR")) AND ("MAINVDTL"."ITEM"="MAINVMSG"."ITEM")) AND ("MAINVDTL"."SUFFIX"="MAINVMSG"."SUFFIX")) LEFT OUTER JOIN "LAWPROD"."POPIVUF" "POPIVUF" ON (("MAINVDTL"."ITEM"="POPIVUF"."ITEM") AND ("MAINVDTL"."VENDOR"="POPIVUF"."VENDOR")) AND ("MAINVDTL"."VEN_ITEM"="POPIVUF"."VEN_ITEM")) ON ((("POPCRUF"."COMPANY"="MAINVMSG"."COMPANY") AND ("POPCRUF"."PO_CODE"="MAINVMSG"."PO_CODE")) AND ("POPCRUF"."PO_NUMBER"="MAINVMSG"."PO_NUMBER")) AND ("POPCRUF"."PO_RELEASE"="MAINVMSG"."PO_RELEASE")) ON "L_HMAG"."L_INDEX"="MAINVMSG"."L_INDEX") ON (((("POLINE"."COMPANY"="MAINVMSG"."COMPANY") AND ("POLINE"."PO_NUMBER"="MAINVMSG"."PO_NUMBER")) AND ("POLINE"."PO_RELEASE"="MAINVMSG"."PO_RELEASE")) AND ("POLINE"."PO_CODE"="MAINVMSG"."PO_CODE")) AND ("POLINE"."LINE_NBR"="MAINVMSG"."PO_LINE_NBR")) INNER JOIN "LAWPROD"."BUYER" "BUYER" ON ("MAINVMSG"."PROCURE_GROUP"="BUYER"."PROCURE_GROUP") AND ("MAINVMSG"."BUYER_CODE"="BUYER"."BUYER_CODE") WHERE ("MAINVMSG"."CREATE_DATE">={ts '2016-01-01 00:00:00'} AND "MAINVMSG"."CREATE_DATE"<{ts '2016-08-02 00:00:00'}) AND "MAINVMSG"."BUYER_CODE"='JAS' AND "POVAGRMTLN"."HOLD_FLAG"='N' ORDER BY "POLINE"."BUYER_CODE"
It looks like this query was created in Crystal, and Crystal is great at overbloating SQL queries. It would be helpful to know what is not working about the new query, whether it generates an error message, stopped returning data, or is returning the wrong data.
Based on what appears to me to be a large number of outer joins being used, my hunch is that you are getting false matches and duplicative/erroneous data.
Generally you only want left outer join or right outer join if a value always exists in one table's column being joined with but the other table column may only potentially contain a value (matches but is not a required field) that way you don't exclude data from your source table where there isn't a match in the joined table. A full outer join will include all data from both table columns in the join regardless of a match and can cause duplicative records. Wherever you have table columns where the field is required on both sides of the join it should use an inner join.
Lawson can also be picky about joining multiple columns in a particular table to get the right data such as with "Company" and whatever else you are trying to join with.
If it were me I would rewrite the query from scratch outside of Crystal step by step and validate the data after setting up each join before setting up another join, or else engage a DBA for assistance.