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
CodePudding user response:
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 |
CodePudding user response:
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');