Query

Question

Hi I have filed "BPLId" ,"DocNum" in OPCH . I have "LocCode" in PCH1 Table. I want those records in which Docnum have different "LocCode" in same Document No Thanks

Emily Foster · Answer

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.