All Forums Database
AJPeyerson 3 posts Joined 07/14
14 Oct 2014
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!

Raja_KT 1246 posts Joined 07/09
16 Oct 2014

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

ulrich 816 posts Joined 09/09
17 Oct 2014

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.