Hello,
I need some help with building a PQL query that will match two different documents by a specific criterion.
Let’s say I have a table with all open and closed items — we’ll call it ‘All_Items’. This table includes various document types like RV, BK, CR, etc.
I’m trying to build a logic in PQL to identify cases where one document type (for example, RV) is matched with another document type (for example, CR) within the same table. The key condition is: The RV document’s Reference Number should match the CR document’s Assignment Number.
I want to write a logic that can detect these matches, possibly using a CASE structure or a domain table approach. Here's what I have so far, but this logic isn’t working since the condition checks if CR document has same AssignmentNumber and ReferenceNumber:
CASE
WHEN "All_Items"."DocumentType" = 'RV'
THEN
CASE
WHEN PU_COUNT(
DOMAIN_TABLE("All_Items"."AssignmentNumber"),
CASE
WHEN "All_Items"."DocumentType" = 'CR'
AND "All_Items"."AssignmentNumber" = "All_Items"."ReferenceNumber"
THEN "All_Items"."AssignmentNumber"
END
) >= 1
THEN ‘Matched)'
ELSE 'No match'
END
END