This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.
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;
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!
Active today
@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?
Active today
@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
Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Introduction
A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
- Acronis
- Databases
- Oracle Database
- MongoDB
- Oracle
- *Backup Software
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses
Course of the Month8 days, 7 hours left to enroll
721 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.