Matching within a window

Database

Matching within a window

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!

2 REPLIES
Enthusiast

Re: Matching within a window

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.

Senior Supporter

Re: Matching within a window

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

select cust_id, 
emp2 as e2,
emp3,
emp4,
max(case when e2 = emp3 then emp3
when e2 = emp4 then emp4
else null
end) over () as e2ine3ore4
from table
qualify e2 = e2ine3ore4

Ulrich