【Question】

I have a table like this:

ID    DDATE
1     2013/5/7
2     2013/8/6
3     2013/6/12
4     2013/12/1
ID values are in an ascending order, so should the DDATE values. Now I want to find the exceptions, like 2 2013/8/6 and 3 2013/6/12.

【Answer】

According to your description, there are two scenarios. One is to compare a record only with its next neighbor (which is your example result 2 and 3). The other is to compare a record with all the other records. In this case, 1,2 and 3 are eligible because DDATE value with ID 3 is earlier than its previous value but later than DDATE with ID 1. You need a window function to handle both scenarios with a subquery in Oracle. The queries are respectively as follows:

A When comparing with the next neighbor:

SELECT id, ddate
 FROM (select id,ddate,
     lag(ddate,1) over(order by id) as d1,
     lead(ddate,1) over(order by id) as d2
     from T0042)
WHERE d1 > ddate OR d2 < ddate

B When comparing with all previous records and the next neighbor:

WITH T1 as (
select id,ddate,max(ddate)over(order by id) d1
from t0042),T2 as (
select id,min(ddate)over(order by id desc) d2
from t0042)
select T1.id,T1.ddate
from T2,T1
WHERE T1.id=T2.id and (t1.ddate<>T1.d1 or t1.ddate<>T2.d2)

Since SQL doesn’t support ordered sets, you need to create sequence numbers for the records using a subquery with the window function. This makes the queries are complicated and hard to understand. SPL (Structured Process Language) provides order-based functions to handle this type of computations. An SPL script is intuitive:

A1: Retrieve records ordered by ID in SQL;

A2: Compare a record with its next neighbor to find one whose DDATE is earlier than the previous one, or one whose DDATE value is later than the next one;

A3: Compare a record with all the previous records and the next neighbor and find one whose DDATE isn’t later than all its previous DDATE values, or one whose DDATE value isn’t earlier than all records following it.

About how to call an SPL script in a Java application, you can refer to How to Call an SPL Script in Java.