Query

Hi there! I see you're looking to extract records from the OPCH and PCH1 tables based on specific criteria. To retrieve records where the same document number (DocNum) has different location codes (LocCode) within the same document, you would typically use a SQL query that involves joining the OPCH and PCH1 tables and applying a condition to filter the results accordingly.

Here is an example SQL query that can help you achieve this:

SELECT OPCH.DocNum, PCH1.LocCode
FROM OPCH
INNER JOIN PCH1 ON OPCH.BPLId = PCH1.BPLId
WHERE EXISTS (
    SELECT 1
    FROM PCH1 P
    WHERE P.BPLId = PCH1.BPLId
    AND P.DocNum = OPCH.DocNum
    GROUP BY P.DocNum
    HAVING COUNT(DISTINCT P.LocCode) > 1
)

In this query:

- We are selecting DocNum from the OPCH table and LocCode from the PCH1 table.

- We are joining the two tables on the common field BPLId.

- The subquery is used to check if there are multiple distinct LocCodes associated with the same DocNum within the PCH1 table.

- If the condition is met (i.e., if there are different LocCodes for the same DocNum), the record will be included in the result set.

You can execute this query in your database management system (DBMS) to retrieve the desired records. Just replace the table and column names with your actual table and column names.

I hope this explanation helps! If you need further clarification or have any more questions, feel free to ask.