Hi All,
I am facing one issue with LAG function, details are as below:
with data1 as
( SELECT FILE_ID,
Line_No,
lag(rtrim(ltrim(LINE_TEXT)),2) over (order by line_no) prev_prev_line_text,
lag(rtrim(ltrim(LINE_TEXT)),1) over (order by line_no) prev_line_text,
Rtrim(rtrim(ltrim(LINE_TEXT))) Line_Text ,
lead(rtrim(ltrim(LINE_TEXT)),1) over (order by line_no) next_Line_Text,
FROM DATA_FILE_TABLE
WHERE file_id= 1234
Order By Line_No)
select * from data1 where line_no=8;
Result:
FILE_ID: 1234
LINE_NO : 8
prev_prev_line_text : "PREV PREV LINE TEXT"
prev_line_text :"PREV LINE TEXT"
Line_Text : LINE TEXT
ISSUE is :
When I create one Record type and bulk collect the data into that for the same query, Result varies to below:
Result:
FILE_ID: 1234
LINE_NO : 8
prev_prev_line_text : "PREV LINE TEXT"
prev_line_text :"PREV PREV LINE TEXT"
Line_Text : LINE TEXT
is there any issue it LAG and BUlk collect into. The same issue is there for LEAD function.