gpsdh
asked on
SQL - How can I get the replacement part description on a query
I have a part_code and a part_desc (Description) and a rep_part(Replacement). There is no specific field in the database for a replacement part description. How could I get that? It is another part of the part master.
Example
Partmaster for Part_code 1234
part_code = 1234 Part_Desc = Numbers Rep_part = ABCD
Partmaster for Part_Code ABCD
part_code = ABCD Part_Desc = Letters Rep_part NULL
In this example the rep_part ABCD in the part master would have a description of Letters
How can I get those on this query?
Example
Partmaster for Part_code 1234
part_code = 1234 Part_Desc = Numbers Rep_part = ABCD
Partmaster for Part_Code ABCD
part_code = ABCD Part_Desc = Letters Rep_part NULL
In this example the rep_part ABCD in the part master would have a description of Letters
How can I get those on this query?
SELECT DISTINCT
ediorderrors.order_numb, CAST(ediorderrors.item_no AS INT) AS line_numb, ediorderrors.error_desc, CAST(ediord.cust_code AS int) AS cust_code, ediord.loaddt,
ediord.part_code AS UPC_CODE, partmstr.part_code AS PART_CODE, partmstr.part_desc AS DESCRIPTION, partmstr.uom AS UOM, ediord.shipto_code,
ordhead.shipname, ediord.qty, partmstr.rep_part,ordhead.order_numb,rep2
FROM ordhead INNER JOIN
ediord ON ordhead.cust_po = ediord.cust_po RIGHT OUTER JOIN
ediorderrors ON ediord.item_no = ediorderrors.item_no AND ediord.cust_po = ediorderrors.order_numb LEFT OUTER JOIN
ediorditem ON ediorditem.item_no = ediord.item_no AND ediorditem.cust_po = ediord.cust_po LEFT OUTER JOIN
partmstr ON partmstr.upc_code = ediord.part_code
WHERE (ediorderrors.cust_code IS NOT NULL) AND (ediord.cust_code <> 'ZZZZZ') AND (ediord.cust_code <> 'PETLAN') AND (ediord.loaddt BETWEEN '09/11/14' AND '09/12/14') AND
(ediord.cust_code <> '068775311CUSPRD') AND error_desc <> 'This is an invalid part ... Please Re-enter.'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER