SQL SELECT statement skips rows based on WHERE statement

0
votes

I apologize, this may be a stupid question! I have created a procedure that maintains, and runs a series of procedures/functions. If error occurs on any procedure an email is sent to me, and an automatic refresh parameter is set with procedure name that failed. Single table houses all procedures (exact names as varchar2) to run in top-down order with looping structure. How can I skip to procedure that has failed with select statement along with grabbing all other procedures underneath procedure that has failed? Basically I would like the procedure to start where it has stopped, and continue the process of running all other procedures. I will appreciate any idea, because I am just learning.

UPDATE

For those confused. I need a SELECT statement that skips rows based on a where and grabs all other rows underneath first row. Basic psuedocode below ...

SELECT procedure_name
FROM table_whatever
SKIP ROWS that procedure_status <> 'completed'
AND grab all rows underneath
WHILST keeping rows in proper order

id  procedure          status
15  table_insert       failed
16  table_update       In Queue
17  email_completion   In Queue

I need to grab failed procedure along with everything underneath.

Any ideas?

Thank you in advance!

2
sqlplsqloracle11goracle-sqldeveloper
Please read the FAQ on how to ask a question on StackOverflow to give the best chance of having it answered: stackoverflow.com/help/how-to-ask. You should have table structures, example data, and expected outcome. - Tom H
Please provide code,nobody enjoys reading some statements and it's not helping,where exactly you have a problem in your procedure? - pouyan021
@TomH Thank you. I will keep this in mind for next time. Unfortunately I cannot show structure, example data, or anything of the like. This is being tested out on my companies testing server in preparation for a certification in pl/sql & Oracle database. I am hoping someone can give a very basic example I may follow, or tell me that this is impossible. - PZNevill
@pouyankhodabakhsh That's fair. Unfortunately I cannot provide based on contract. Just need to know if such a SELECT statement is possible. - PZNevill
Sadly this is our lives, nit-picking each other's answers to death. Er-I mean helping others and learning something ourselves in the process! Happy New Year all! :-) - Gary_W

2 Answers

3
votes

I'll bite, what the heck. Based on your data above which assumes there can be only one row with a status of 'failed', and assumes procedures are in 'id' order:

SELECT procedure
FROM   table_whatever
WHERE  id >= (SELECT ID
              FROM   table_whatever
              where  status = 'failed')
ORDER by id;
0
votes

Based on your minimal pseudocode, and assuming the 'proper order' is by ID, you can just exclude those that are completed:

SELECT procedure_name
FROM table_whatever
WHERE procedure_status <> 'completed'
ORDER BY id;

If you explicitly want to look for 'failed' - or so the same query can be used to kick off the sequence before anything has run - you could use an analytic query and inline view, so you only have to hit the table once:

SELECT procedure_name
FROM (
  SELECT id, procedure_name, procedure_status,
    MIN(CASE WHEN procedure_status = 'failed' THEN id END) OVER () AS failed_id
  FROM table_whatever
)
WHERE id >= COALESCE(failed_id, 0)
ORDER BY id;

For a small table hitting it twice probably isn't too big a deal, so Gary_W's subquery approach would work just as well...