I have a table that records transactions sent from our web service. I would like to obtain out of sequence numbers between one number and the latest number in the table for that day.
This is a simplified structure: Table name CARTREQUESTS
If you dont need the above, please let me know I didnt get it completely.
johnsone
This should do it:
SELECT cr_order_id - 1 FROM (SELECT cr_order_id, Lag(cr_order_id) over ( ORDER BY cr_order_id) prev_cr_order_id FROM cartrequests WHERE cr_date BETWEEN Trunc(SYSDATE - 1) AND Trunc(SYSDATE) - 1 / 86400 ) WHERE cr_order_id != prev_cr_order_id + 1;
SELECT CASE WHEN a.GStart >= b.Gend THEN b.Gend ELSE a.GStart END MissingCR_ORDER_IDs FROM ( SELECT MIN(CR_ORDER_ID) - 1 GStart, MAX(CR_ORDER_ID) + 1 Gend, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnks FROM (SELECT CR_ORDER_ID ,CR_ORDER_ID - ROW_NUMBER() OVER (ORDER BY CR_ORDER_ID) rnk FROM CARTREQUESTS WHERE cr_date BETWEEN Trunc(SYSDATE - 1) AND Trunc(SYSDATE) - 1 / 86400 )y Group By Rnk) a INNER JOIN ( SELECT MIN(CR_ORDER_ID) - 1 GStart, MAX(CR_ORDER_ID) + 1 Gend, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnks FROM (SELECT CR_ORDER_ID ,CR_ORDER_ID - ROW_NUMBER() OVER (ORDER BY CR_ORDER_ID) rnk FROM CARTREQUESTS WHERE cr_date BETWEEN Trunc(SYSDATE - 1) AND Trunc(SYSDATE) - 1 / 86400 )y Group By Rnk) b ON a.rnks-1 = b.rnks--
SELECT v1 GapStart as MissingCR_ORDER_IDs FROM( SELECT CR_ORDER_ID+1 v1 ,LEAD(CR_ORDER_ID) OVER(ORDER BY CR_ORDER_ID)-1 v2 FROM CARTREQUESTS WHERE cr_date BETWEEN Trunc(SYSDATE - 1) AND Trunc(SYSDATE) - 1 / 86400)tWHERE v2>=v1--
You don't need to post it all. Just add to the test case so that it simulates what you are seeing.
>>still testing others
Why? I have already pointed out they have problems if you are missing more than 1 in a gap.
awking00
select (select min(cr_order_id) from cartrequests where cr_date > trunc(sysdate - 1)) + level - 1
from dual
connect by level <= (select max(cr_order_id) - min(cr_order_id) from cartrequests where cr_date > trunc(sysdate - 1))
minus
select cr_order_id from cartrequests where cr_date > trunc(sysdate - 1);
Pawan Kumar
Great awking00 -- I was expecting someone to use the numbers table. Cool :)
The connect by method works regardless of how many gaps. It in essence generates a "gapless" list then substracts the existing values to determining the missing ones.
awking00
Sorry, slightwv. I never noticed your post that used the connect by method.
Open in new window