need to find the consecutive rows where the status is AV prior to VL but receiving a missing SELECT

Question

I am trying to retrieve consecutive rows where the ls_status is appearing in this order per letter eg i want to see the entries where status 'AV' is occurring prior 'VL'

in my case i need to see rows 17 and 18 in my results set: (https://i.stack.imgur.com/TALU5.png)

Below is the executed sql. if i run both subqueries separately there is no problem but when i run the entire piece of code i receive error: ORA-00928: missing SELECT keyword 00928. 00000 - "missing SELECT keyword" *Cause:
*Action: Error at Line: 9 Column: 35

Any idea why this is??

with ls_ital as
    (select ls_key, ls_letter, ls_status, ls_insertdate, row_number() over (order by ls_key) as id
    from letter_status_aital),
  t1 as  
    (select ls_key, ls_letter, ls_status, ls_insertdate,
    row_number() over(order by id) as rn,
    id -(row_number() over(order by id)) as diff
    from ls_ital
    where ls_status in ('AV','VL'));

i tried to run both subqueries separately and they succeed but when i run both i get the

ORA-00928: missing SELECT keyword 00928. 00000 - "missing SELECT keyword" *Cause:
*Action: Error at Line: 9 Column: 35

see the results of the 1st query: enter image description here

i am not sure if i am on the right track with the query but i just need to retrieve rows 17 and 18 from the 1st screen shot that is when a status 'AV' occurs before 'VL' sequentially

Answer 1

If you fix the syntax error in your code, which is using a sub-query factoring clause without a final SELECT clause that follows the sub-queries (which you can either append or convert your final sub-query to):

with ls_ital as (
  select ls_key,
         ls_letter,
         ls_status,
         ls_insertdate,
         row_number() over (order by ls_key) as id
  from   letter_status_aital
)
select ls_key,
       ls_letter,
       ls_status,
       ls_insertdate,
       row_number() over(order by id) as rn,
       id -(row_number() over(order by id)) as diff
from   ls_ital
where  ls_status in ('AV','VL');

Then your code is not going to do what you expect because you are not checking that AV comes before VL and you are not doing any filtering on diff to check that groups of consecutive rows are being returned.

---

From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row processing which will be much simpler (and probably more efficient):

SELECT *
FROM   letter_status_aital
MATCH_RECOGNIZE(
  ORDER BY ls_key
  ALL ROWS PER MATCH
  PATTERN ( av vl )
  DEFINE
    av AS ls_status = 'AV',
    vl AS ls_status = 'VL'
);

Which, for the sample data:

create table letter_status_aital(
  ls_letter,
  ls_bydoctor,
  ls_status,
  ls_insertdate,
  ls_key
) AS
SELECT 1714, 1035, 'AP', DATE '2022-08-19', 4801 FROM DUAL UNION ALL
SELECT 1714, 1035, 'AV', DATE '2022-08-19', 4803 FROM DUAL UNION ALL
SELECT 1714, 1035, 'VL', DATE '2022-08-22', 4837 FROM DUAL UNION ALL
SELECT 1714, 1035, 'AP', DATE '2022-08-22', 4842 FROM DUAL UNION ALL
SELECT 1714, 1035, 'VL', DATE '2022-08-22', 4844 FROM DUAL;

Outputs:

LS_KEY	LS_LETTER	LS_BYDOCTOR	LS_STATUS	LS_INSERTDATE
4803	1714	1035	AV	19-AUG-22
4837	1714	1035	VL	22-AUG-22

fiddle

Answer 2

If that's it (I mean, query you posted is supposed to return result you want), either select from t1:

with ls_ital as
    (select ls_key, ls_letter, ls_status, ls_insertdate, row_number() over (order by ls_key) as id
    from letter_status_aital),
  t1 as  
    (select ls_key, ls_letter, ls_status, ls_insertdate,
    row_number() over(order by id) as rn,
    id -(row_number() over(order by id)) as diff
    from ls_ital
    where ls_status in ('AV','VL'))
select * from t1;

or get rid of it (as a CTE):

with ls_ital as
    (select ls_key, ls_letter, ls_status, ls_insertdate, row_number() over (order by ls_key) as id
    from letter_status_aital)
select ls_key, ls_letter, ls_status, ls_insertdate,
    row_number() over(order by id) as rn,
    id -(row_number() over(order by id)) as diff
    from ls_ital
    where ls_status in ('AV','VL');