I have one requirement where I need to find the first missing number in the List which is not there in Database(Oracle) column.
Scenario is like this :
Table 1:
Link to table image is here :-
https://i.stack.imgur.com/iXm6N.png
From the above table I am thinking to make 3 lists
List<Integer> lst1 = new ArrayList<Integer>();
List<Integer> lst2 = new ArrayList<Integer>();
List<Integer> lst3 = new ArrayList<Integer>();
lst1 -> [0,1,2,3,4,5,6....1000]
lst2 -> [a0,a1,a2,a3,a4,a5,a6....a1000]
lst3 -> [b0,b1,b2,b3,b4,b5,b6....b1000]
As of now the lists contains approx 1000 values in the serial order.
Now I have a Database table as below
Link to table image :-
https://i.stack.imgur.com/OvAmW.png
How can I match the lsts with Range column. I need to find what value from lsts is not present in this table ?
Like in this case, if we see from the "lst1" the first available value which is not there in the table is "1" then next available value is "3" and then 5.6...so on. Similarly for "lst2" the first missing value is "a3"
Is there any way to do ?
-----------------------------------------------------------------
Someone suggested a solution on stackoverflow as below:-
-----------------------------------------------------------------
The below query obtains a prefix and numeric valuest of range_start and range_end.
For simplicity of examples I limited ranges to 0-5
SELECT lstname,
regexp_substr( rangestart, '[^0-9]') AS Prefix,
regexp_substr( rangestart, '[0-9]') AS r_start,
regexp_substr( rangeend, '[0-9]') AS r_end
FROM table_1
LSTNAME |PREFIX |R_START |R_END |
--------|-------|--------|------|
Lst1 | |0 |5 |
Lst2 |a |0 |5 |
Lst3 |b |0 |5 |
The below query will generate all values for ranges using the above query as a subquery.
Please note that CROSS JOIN LATERAL works on version Oracle 12c and later, if you are using earlier version then this query must be rewriten.
SELECT lstname, prefix || val AS val
FROM (
SELECT lstname,
regexp_substr( rangestart, '[^0-9]') AS Prefix,
regexp_substr( rangestart, '[0-9]') AS r_start,
regexp_substr( rangeend, '[0-9]') AS r_end
FROM table_1
) x
CROSS JOIN LATERAL (
SELECT LEVEL - 1 + x.r_start AS val
FROM dual
CONNECT BY LEVEL <= x.r_end - x.r_start + 1
)
LSTNAME |VAL |
--------|----|
Lst1 |0 |
Lst1 |1 |
Lst1 |2 |
Lst1 |3 |
Lst1 |4 |
Lst1 |5 |
Lst2 |a0 |
Lst2 |a1 |
Lst2 |a2 |
Lst2 |a3 |
Lst2 |a4 |
Lst2 |a5 |
Lst3 |b0 |
Lst3 |b1 |
Lst3 |b2 |
Lst3 |b3 |
Lst3 |b4 |
Lst3 |b5 |
And now say that table_2 contains the following values:
SELECT * FROM table_2
RANGE |
------|
3 |
a0 |
a1 |
a5 |
b3 |
b4 |
b5 |
To find missing values just LEFT JOIN the above queries to this table and filter out not null values.
Please note that I am using "RANGE" within quotes as a column name in this example because RANGE is reserved word in Oracle
SELECT lstname, val
FROM (
SELECT lstname, prefix || val AS val
FROM (
SELECT lstname,
regexp_substr( rangestart, '[^0-9]') AS Prefix,
regexp_substr( rangestart, '[0-9]') AS r_start,
regexp_substr( rangeend, '[0-9]') AS r_end
FROM table_1
) x
CROSS JOIN LATERAL (
SELECT LEVEL - 1 + x.r_start AS val
FROM dual
CONNECT BY LEVEL <= x.r_end - x.r_start + 1
)
) XX
LEFT JOIN table_2 t2
ON t2."RANGE" = xx.val
WHERE t2."RANGE" IS NULL
ORDER BY 1, 2;
LSTNAME |VAL |
--------|----|
Lst1 |0 |
Lst1 |1 |
Lst1 |2 |
Lst1 |4 |
Lst1 |5 |
Lst2 |a2 |
Lst2 |a3 |
Lst2 |a4 |
Lst3 |b0 |
Lst3 |b1 |
Lst3 |b2 |
---------------
I think above solution should work. But the problem is "CROSS JOIN LATERAL" thing. I am using Oracle 11g and this keyword is not working in 11g.
Does anyone knows what will be the alternative of this in 11g or any other alternative solution to the whole problem ?
Thanks
Nitin