CODEWITHSUNDEEP
sqloracle-database
Alok Ranjan Swain
Alok Ranjan Swain

Reputation: 119

Fetching next row based on data of current row

I have one sql table something like this

fist_name|last_name
asd      | fgh
qwer     | ijkl
yuio     | zsef
...      |  ....

The data is arranged sequentially like the above,

I am assigning the retrieved value to some string (let Str) for each row.

I want to create a function with parameters (String str, mode) such that ,

if mode = 'GET', It will assign the first row to the string,

and

if mode = 'NEXT', It will read the string then assign the next row to the string

and return the string. I have DB on oracle. Also I can't modify the DB (else I would have add some ID related to each entry)

It could be done using if-else for small table, but for large table it's not feasible. Is there some way by which I could get the exact row_number assigned to each rows.

eg:
f1("qwerijkl","NEXT) => "yuiozssef";
f1("",'GET') -> "asdfgh";

Upvotes: 0

Views: 1610

Answers (3)

astentx
astentx

Reputation: 6751

You may use subquery with offset ... fetch first ... with additional check of previous value. But if you need to process all the values, then it would be better to open a cursor with order by, then fetch row by row and process the result.

Below is the code. You may use the query from function definition and replace parameters with bind variables.

select *
from t

FIRST_NAME | LAST_NAME
:--------- | :--------
0001       | 1        
0002       | 22       
0003       | 333      
0004       | 4444     
0005       | 55555    
0006       | 666666   
0007       | 7777777  
0008       | 88888888 
0009       | 999999999

with function f(
  p_name varchar2
  , p_method varchar2
) return varchar2
as
  l_res varchar2(1000);
begin

  with a as (
    select
      first_name || last_name as res
      , lag(first_name || last_name)
        over(order by first_name || last_name)
        as prev_
    from t
    where first_name || last_name >= p_name
      or p_method = 'GET'
    order by 1
    offset decode(p_method, 'GET', 0, 'NEXT', 1) rows
    fetch next 1 rows only
  )
  select max(res)
    into l_res
  from a

. /Additional check of previous value/ where prev_ = p_name or p_method = 'GET' ;

  return l_res;
  
end;

select
  f('', 'GET') as get1,
  f('qwe', 'GET') as get2,
  f('000222', 'NEXT') as next1,
  f('12', 'NEXT') as next2
from dual

GET1  | GET2  | NEXT1   | NEXT2
:---- | :---- | :------ | :----
00011 | 00011 | 0003333 | null

db<>fiddle here

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

You need an order. first_name, last_name or last_name, first_name would be appropriate choices here.

A function could look like this:

CREATE OR REPLACE FUNCTION get_name
(
  p_mode       in      varchar2,
  p_firstname  in out  varchar2,
  p_lastname   in out  varchar2
) RETURN VARCHAR2 AS
BEGIN
  SELECT p_firstname, p_lastname
  INTO p_firstname, p_lastname
  from names
  WHERE p_mode = 'GET'
     OR firstname > p_firstname
     OR (firstname = p_firstname and lastname > p_lastname)
  ORDER BY firstname, lastname
  FETCH FIRST ROW ONLY;

  RETURN 'okay';
EXCEPTION WHEN NO_DATA_FOUND THEN
  RETURN 'no more rows';
END get_name;

(It may be faster, though, to create a materialized view instead with ROW_NUMBER where GET would simply get the row #1, while NEXT would retrieve the row number for the name given and select the name for the following number.)

Here is a script to test above function:

DECLARE
  v_firstname varchar2(100);
  v_lastname varchar2(100);
  v_result varchar2(100);
BEGIN
  v_result := get_name('GET', v_firstname, v_lastname);
  IF v_result = 'okay' then
    dbms_output.put_line(v_firstname || ' ' || v_lastname);
  END IF;

  WHILE v_result = 'okay' LOOP
    v_result := get_name('NEXT', v_firstname, v_lastname);
    IF v_result = 'okay' then
      dbms_output.put_line(v_firstname || ' ' || v_lastname);
    END IF;
  END LOOP;  
END;

Upvotes: 1

nayi224
nayi224

Reputation: 565

Sorting using rowid is not good, you'd better set another sort column

select aa into res from (
    select t1.first_name || t1.last_name aa
      from 
    (
    select * 
      from tab t1 
     where 'GET' = P_MODE
     order by t1.rowid
    ) t1
     where rownum = 1
     
    union all

select t1.res
  from (
    select lead(t1.first_name || t1.last_name) over(order by t1.rowid) res,
         t1.*
      from tab1 t1
     where 'NEXT' = P_MODE
) t1
 where t1.first_name || t1.last_name = P_STR
) t1

If you use a function, you can put this SQL into the function, or you can query it directly.

Upvotes: 1

Related Questions