Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Out of Sequence numbers for today
Posted on 2016-10-03
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
9
6-
4
- +2
25 Comments
Active today
May be this ..
SELECT
TOP 2
CR_ID,
CR_DATE,
CR_ORDER_ID,
CR_CUSTOMERID
FROM
CARTREQUESTS
WHERE
CR_DATE <= SYSDATE-1
AND
CR_ORDER_ID > 100148443
Active today
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;
![[eBook] Windows Nano Server](https://filedb.experts-exchange.com/files/public/2017/6/24/1329f749-2f43-4bd1-8413-3b5b5ab33fc6.png)
Active today
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.
Active today
@johnsone & slightwv (䄆 Netminder) - Thanks for helping. Just moved from SQL Server. :) My bad!
@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.
Active today
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
--
Active today
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
--
Active today
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
/
Active today
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
Active today
>>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.
Active today
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
Active today
So, I'm confused. Are we basing this on date, a starting id, or what?
Working on a more than 1 gap.
Working on a more than 1 gap.
Active today
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;
Active today
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.
Active today
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);
Active today
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.
Featured Post
With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Background
In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself).
I will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, 13 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
632 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.