Oracle SQL Sequence number in WHERE clause

Question

What can I do if I wish to use a sequence number in a WHERE clause?

Here I wanna use the last value gebarated by the sequence in the WHERE clause

CREATE SEQUENCE my_no_s
    INCREMENT BY 5
    START WITH 100;

INSERT INTO myTable VALUES (
    my_no_s.NEXTVAL,
    TO_DATE('2023/10/03', 'yyyy/mm/dd')
    3
);

INSERT INTO myTable (m_no, m_datetime, m_roomno)
SELECT  my_no_s.NEXTVAL,
        my_datetime + 10,
        my_roomno
FROM myTable
WHERE my_no = my_no_s.CURRVAL - 5;

Answer 1

You can use anonymous PL/SQL block

DECLARE
seq_curr_value NUMBER;
BEGIN

  -- Select current value of sequence
  SELECT my_no_s.CURRVAL INTO seq_curr_value FROM dual;
  
  INSERT INTO myTable (m_no, m_datetime, m_roomno)
  SELECT  my_no_s.NEXTVAL,
          m_datetime + 10,
          m_roomno
  FROM myTable
  WHERE m_no = seq_curr_value - 5;
END;
/

PS: You have to edit your column names in select list also.
Example: not my_datetime but m_datetime and so on.