PL/SQL Getting a list of sequence values

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?

Thanks in advance.
taylort2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
To clarify the requirement:
Return any values in sequence before and after some starting value until a gap is reached.
0
Gerwin Jansen, EE MVETopic Advisor Commented:
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
sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
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
sdstuberCommented:
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.

If your data values have some other pattern please elaborate and I'll adjust the queries accordingly.
0
taylort2Author Commented:
Yes, slightwv, it goes until a gap is reached.  It could be 1 or a thousand in between.
0
Mark GeerlingsDatabase AdministratorCommented:
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
slightwv (䄆 Netminder) Commented:
>>Yes, slightwv, it goes until a gap is reached.  It could be 1 or a thousand in between.

Start with what sdstuber posted in http:#a39449085 and go from there.
0
taylort2Author Commented:
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
taylort2Author Commented:
markgeer,

the values will be returned to the user
0
sdstuberCommented:
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
taylort2Author Commented:
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
sdstuberCommented:
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
taylort2Author Commented:
Please Delete question
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.