An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
Out of Sequence numbers for today
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
There are CR_ORDERID values greater than 100148466 at this time, I just want processed records for SYSDATE-1
This is a simplified structure: Table name CARTREQUESTS
CR_ID
CR_DATE
CR_ORDER_ID
CR_CUSTOMERID
CR_ID CR_DATE CR_ORDER_ID CR_CUSTOMERID
1540668 9/9/2016 11:59:52 AM 100148466 1087840
1540667 9/9/2016 11:58:49 AM 100148465 1191293
1540687 9/9/2016 12:23:07 PM 100148464 916470
1540647 9/9/2016 11:46:32 AM 100148463 918195
1540629 9/9/2016 11:45:08 AM 100148462 957676
1540628 9/9/2016 11:44:28 AM 100148461 1161585
1540627 9/9/2016 11:43:48 AM 100148460 956791
1540607 9/9/2016 11:40:47 AM 100148459 1161585
1540567 9/9/2016 11:36:07 AM 100148457 1127193
1540548 9/9/2016 11:30:42 AM 100148456 1204204
1540547 9/9/2016 11:29:11 AM 100148455 967946
1540527 9/9/2016 11:27:25 AM 100148454 1209116
1540507 9/9/2016 11:21:54 AM 100148453 1207376
1540488 9/9/2016 11:18:17 AM 100148452 1057770
1540467 9/9/2016 11:02:16 AM 100148450 1216001
1540448 9/9/2016 11:00:51 AM 100148449 1082302
1540447 9/9/2016 11:00:39 AM 100148448 1082298
1540427 9/9/2016 10:58:10 AM 100148447 951007
1540407 9/9/2016 10:56:10 AM 100148446 1209338
1540387 9/9/2016 10:51:08 AM 100148445 1205880
1540367 9/9/2016 10:39:12 AM 100148444 1061172
1540347 9/9/2016 10:34:37 AM 100148443 1215973
There are CR_ORDERID values greater than 100148466 at this time, I just want processed records for SYSDATE-1
Asked:
9
6-
4
- +2
3 Solutions
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;
Pawan,
TOP isn't Oracle.
It also doesn't answer the question. They are looking for gaps in the numbers.
TOP isn't Oracle.
It also doesn't answer the question. They are looking for gaps in the numbers.
@Pawan - no, you didn't get it. I don't just want the top 2, I want the ones that are missing from the sequence.
@johnsone - Where do I pass the latest id that I have, the beginning id?
@johnsone - Where do I pass the latest id that I have, the beginning id?
@johnsone, that also didn't work because when I changed the dates to 10 days ago, it showed one record that was already there.
Try this sir..
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
--
May be this one also..
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
)t
WHERE v2>=v1
--
I think the above examples will have problems if there is a gap of more than 1.
Here is my attempt:
Here is my attempt:
select minid+(level-1) from (
select max(cr_order_id) maxid, min(cr_order_id) minid
from CARTREQUESTS
WHERE cr_date >= to_date('9/9/2016','MM/DD/YYYY') and cr_date < to_date('9/9/2016','MM/DD/YYYY')+1
)
connect by level <= maxid-minid+1
minus
select cr_order_id from CARTREQUESTS
/
With full testing complete code [Where clause of dates is commented.. Please uncomment]
/*
** SOLUTION 1
*/
/*
** SOLUTION 2
*/
Output
-----------------------
MissingCR_ORDER_IDs
100148451
100148458
Enjoy
CREATE TABLE test
(
CR_ID BIGINT
,CR_DATE DATETIME
,CR_ORDER_ID BIGINT
,CR_CUSTOMERID BIGINT
)
GO
INSERT INTO test VALUES
(1540668 ,'9/9/2016 11:59:52 AM', 100148466 , 1087840),
(1540667 ,'9/9/2016 11:58:49 AM', 100148465 , 1191293),
(1540687 ,'9/9/2016 12:23:07 PM', 100148464 , 916470),
(1540647 ,'9/9/2016 11:46:32 AM', 100148463 , 918195),
(1540629 ,'9/9/2016 11:45:08 AM', 100148462 , 957676),
(1540628 ,'9/9/2016 11:44:28 AM', 100148461 , 1161585),
(1540627 ,'9/9/2016 11:43:48 AM', 100148460 , 956791),
(1540607 ,'9/9/2016 11:40:47 AM', 100148459 , 1161585),
(1540567 ,'9/9/2016 11:36:07 AM', 100148457 , 1127193),
(1540548 ,'9/9/2016 11:30:42 AM', 100148456 , 1204204),
(1540547 ,'9/9/2016 11:29:11 AM', 100148455 , 967946),
(1540527 ,'9/9/2016 11:27:25 AM', 100148454 , 1209116),
(1540507 ,'9/9/2016 11:21:54 AM', 100148453 , 1207376),
(1540488 ,'9/9/2016 11:18:17 AM', 100148452 , 1057770),
(1540467 ,'9/9/2016 11:02:16 AM', 100148450 , 1216001),
(1540448 ,'9/9/2016 11:00:51 AM', 100148449 , 1082302),
(1540447 ,'9/9/2016 11:00:39 AM', 100148448 , 1082298),
(1540427 ,'9/9/2016 10:58:10 AM', 100148447 , 951007),
(1540407 ,'9/9/2016 10:56:10 AM', 100148446 , 1209338),
(1540387 ,'9/9/2016 10:51:08 AM', 100148445 , 1205880),
(1540367 ,'9/9/2016 10:39:12 AM', 100148444 , 1061172),
(1540347 ,'9/9/2016 10:34:37 AM', 100148443 , 1215973)
/*
** SOLUTION 1
*/
SELECT v1 MissingCR_ORDER_IDs FROM
(
SELECT CR_ORDER_ID+1 v1 ,LEAD(CR_ORDER_ID) OVER(ORDER BY CR_ORDER_ID)-1 v2
FROM test
--WHERE cr_date BETWEEN Trunc(SYSDATE - 1) AND Trunc(SYSDATE) - 1 / 86400
)t
WHERE v2>=v1
/*
** SOLUTION 2
*/
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 test
--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 test
--WHERE cr_date BETWEEN Trunc(SYSDATE - 1) AND Trunc(SYSDATE) - 1 / 86400
)y
Group By Rnk) b ON a.rnks-1 = b.rnks
Output
-----------------------
MissingCR_ORDER_IDs
100148451
100148458
Enjoy
>>With full testing complete code
Tested using Oracle? Hint: There is still invalid Oracle syntax in there.
Now retest and don't include:
(1540467 ,'9/9/2016 11:02:16 AM', 100148450 , 1216001),
This creates a gap of two numbers missing.
Tested using Oracle? Hint: There is still invalid Oracle syntax in there.
Now retest and don't include:
(1540467 ,'9/9/2016 11:02:16 AM', 100148450 , 1216001),
This creates a gap of two numbers missing.
In the above case sir we can use something like below...
SELECT v1 StartingMissingCR_ORDER_IDs , v2 EndMissingCr_Order_No FROM
(
SELECT CR_ORDER_ID+1 v1 ,LEAD(CR_ORDER_ID) OVER(ORDER BY CR_ORDER_ID)-1 v2
FROM test
--WHERE cr_date BETWEEN Trunc(SYSDATE - 1) AND Trunc(SYSDATE) - 1 / 86400
)t
WHERE v2>=v1
So this will give you the starting and ending number for each gap. Is that enough?
Not sure what we are looking at for universe, so you have to fill in the where clause in the middle where the ??? is.
Not sure what we are looking at for universe, so you have to fill in the where clause in the middle where the ??? is.
SELECT prev_cr_order_id + 1 start_gap,
cr_order_id - 1 end_gap
FROM (SELECT cr_order_id,
Lag(cr_order_id)
over (
ORDER BY cr_order_id) prev_cr_order_id
FROM cartrequests
WHERE ???)
WHERE cr_order_id != prev_cr_order_id + 1;
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.
>>still testing others
Why? I have already pointed out they have problems if you are missing more than 1 in a gap.
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);
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);
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.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
Open in new window