Solved

# PL/SQL Getting a list of sequence values

Posted on 2013-08-29
Medium Priority
150 Views
I am looking for a way to get a list of data based on what a user enters.

User Enters: A1001

Data
====
A0999
A1001
A1002
A1003
A1005
A1011
A1012
A1013

I would like to get all the numbers that are in sequence (A1001, A1002, & A1003) using the entered value as the starting point.

Also, is it possible to get the same list(A1001, A1002, & A1003) if the user entered A1002 instead?

0
Question by:taylort2
• 5
• 5
• 2
• +2

LVL 78

Expert Comment

ID: 39448889
To clarify the requirement:
Return any values in sequence before and after some starting value until a gap is reached.
0

LVL 38

Expert Comment

ID: 39448915
You'd have to define the 'gap' by stripping part of what the user enters and then use that part as a wildcard. For example: user enters A1021 - strip last 2 characters - you get A10 - then select records like A10%

Would this work for you? This assuming Data is a table that contains your records.
0

LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 39448975
To find the sequences that start with a particular value try this....

simply change "data" and "yourtable" to your real column and table names...

SELECT data
FROM (SELECT data,
LAST_VALUE(
CASE
WHEN TO_NUMBER(REGEXP_SUBSTR(data, '[0-9]+')) !=
TO_NUMBER(REGEXP_SUBSTR(prevdata, '[0-9]+')) + 1
THEN
data
END IGNORE NULLS
)
OVER (ORDER BY data)
seq_first
FROM (SELECT data,
LEAD(data) OVER (ORDER BY data) nextdata,
LAG(data) OVER (ORDER BY data) prevdata
FROM yourtable)
WHERE TO_NUMBER(REGEXP_SUBSTR(data, '[0-9]+')) =
TO_NUMBER(REGEXP_SUBSTR(prevdata, '[0-9]+')) + 1
OR TO_NUMBER(REGEXP_SUBSTR(data, '[0-9]+')) =
TO_NUMBER(REGEXP_SUBSTR(nextdata, '[0-9]+')) - 1)
WHERE seq_first = 'A1001'
0

LVL 74

Assisted Solution

sdstuber earned 2000 total points
ID: 39449016
To check for any member of a sequence try this...

SELECT data
FROM (SELECT data, COLLECT(data) OVER (PARTITION BY seq_first) x
FROM (SELECT data,
LAST_VALUE(
CASE
WHEN TO_NUMBER(REGEXP_SUBSTR(data, '[0-9]+')) !=
TO_NUMBER(REGEXP_SUBSTR(prevdata, '[0-9]+')) + 1
THEN
data
END IGNORE NULLS
)
OVER (ORDER BY data)
seq_first
FROM (SELECT data,
LEAD(data) OVER (ORDER BY data) nextdata,
LAG(data) OVER (ORDER BY data) prevdata
FROM yourtable)
WHERE TO_NUMBER(REGEXP_SUBSTR(data, '[0-9]+')) =
TO_NUMBER(REGEXP_SUBSTR(prevdata, '[0-9]+')) + 1
OR TO_NUMBER(REGEXP_SUBSTR(data, '[0-9]+')) =
TO_NUMBER(REGEXP_SUBSTR(nextdata, '[0-9]+')) - 1))
WHERE 'A1002' MEMBER OF CAST(x AS ora_mining_varchar2_nt)
0

LVL 74

Expert Comment

ID: 39449085
here's a little more compact and efficient version of each...

SELECT data
FROM (SELECT data,
LAST_VALUE(
CASE
WHEN TO_NUMBER(REGEXP_SUBSTR(data, '[0-9]+')) !=
TO_NUMBER(REGEXP_SUBSTR(prevdata, '[0-9]+')) + 1
THEN
data
END IGNORE NULLS
)
OVER (ORDER BY data)
seq_first
FROM (SELECT data, LAG(data) OVER (ORDER BY data) prevdata FROM yourtable))
WHERE seq_first = 'A1001'

SELECT data
FROM (SELECT data, COLLECT(data) OVER (PARTITION BY seq_first) x
FROM (SELECT data,
LAST_VALUE(
CASE
WHEN TO_NUMBER(REGEXP_SUBSTR(data, '[0-9]+')) !=
TO_NUMBER(REGEXP_SUBSTR(prevdata, '[0-9]+')) + 1
THEN
data
END IGNORE NULLS
)
OVER (ORDER BY data)
seq_first
FROM (SELECT data,
LAG(data) OVER (ORDER BY data) prevdata
FROM yourtable)
))
WHERE 'A1002' MEMBER OF CAST(x AS ora_mining_varchar2_nt)

note, to find sequences I'm only looking at the numeric portion of the data.

If you might have

A0001
B0001
A0002
B0002
A0004

that would be a problem, it's solvable, but the code above doesn't do that.
Also I assume the sequence values are unique.

0

Author Comment

ID: 39449310
Yes, slightwv, it goes until a gap is reached.  It could be 1 or a thousand in between.
0

LVL 36

Expert Comment

ID: 39449313
You asked "for a way to get a list of data based on what a user enters".  You didn't indicate whether the efficiency of the search (or response time for the user) is important or not.  Whenever you ask Oracle to evaluate just a portion of a column value, especially if that portion does not include the first character of the string, you run the risk of forcing a (slow) full-table scan.  Of course, your data volumes in this table are a big factor.  If this table is small, this may not be a problem for you.

You also suggested from your question title that this will be used in PL\SQL, but you did not indicate what PL\SQL should do with the list of records retrieved.  Will these valus just be used inside a PL\SQL block or procedure, or will they actually be returned to the user (or to a calling program)?

What is the business problem you are trying to solve here?  (There may be a better, simpler or more-efficient option.)  Do you have any control over the application code that creates these records?
0

LVL 78

Expert Comment

ID: 39449324
>>Yes, slightwv, it goes until a gap is reached.  It could be 1 or a thousand in between.

0

Author Comment

ID: 39449334
the only thing I know of it that the field could be 20 characters long.

So you could have a something like:

A1001
A1002
A1003
A1000004
B1004

The sequence should stop at the A1003
0

Author Comment

ID: 39449340
markgeer,

the values will be returned to the user
0

LVL 74

Expert Comment

ID: 39449359
A1001
A1002
A1003
A1000004
B1004

As long as the format is always a single letter followed by digits and the values are unique, then what I posted in http:#a39449085  should work.

If it is always one letter followed by digits, it will be more efficient to replace

REGEXP_SUBSTR(data, '[0-9]+')

with

SUBSTR(data,2)

but otherwise both of the queries should work fine
0

Author Comment

ID: 39449364
If I wasn't clear enough, please let me know.  I am doing a number of thing at the same time and I hurried to write the post.
0

LVL 74

Expert Comment

ID: 39449374
Just try the queries I posted.

If they don't work - please provide sample data that shows the error along with expected results.

You don't need to post the wrong results.
0

Author Comment

ID: 39865000
0

## Featured Post

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.