Using a sample table as such:
CUST_ID EMP1 EMP2 EMP3 EMP4
1 E1 NULL E2 NULL
1 E1 E2 NULL NULL
1 E4 E1 NULL E5
I need to find customer ID's where EMP2 equals either EMP3 or EMP4. In this instance, I need to find either (or both) of the first two records.
I can't change the information, columns, etc. This won't be a production query, rather a fact-finding mission as the two that were observed accidentally show an error in a table that uses this as its source. I need to identify all of those records. I was using a partition on CUST_ID and EMP1, as EMP1 was believed to be irrelevant to the error (unless EMP1 = EMP2/3/4).
Oh, and the source table runs around 12,000,000 rows.
Any good ideas on this?
Thanks in advance!
It will take sometime 12,000,000 rows, but not much.
self-join, recursive query , but I think it will give spool memory. Maybe you can think of getting into vtt or gtt, filtering what you want and do your fact-finding.
the requiremet is a bit unclear to me - is the condition to be meet within a single row or accross multiple rows?
the first case should be clear
the second case cold be solved with OLAP functions
emp2 as e2,
max(case when e2 = emp3 then emp3
when e2 = emp4 then emp4
end) over () as e2ine3ore4
qualify e2 = e2ine3ore4