Return latest record

Return latest record
0

#1

Hello, i need to understand what if anything in this function created by someone else in the company before
me is wrong as for it is not returning the latest record i’ve been told . I found out in some other forum issues that they suggested to use the max(dateColumn)
instead of “row_numer = 1” for example but not quite sure how to and where to incorporate that.

– Knowing that – We use Oracle version 12, CustomObjectTypeA is an custom Oracle OBJECT TYPE defined by some old employee not longer in here,
V_OtherView is of Table_Mnd type beeing defined by some old employee not longer in here, V_ABC_123 is a view created by some old employee not longer
in here as well.

CREATE OR REPLACE FUNCTION F_TABLE_APPROVED (NUMBER_F_UPD number, NUMBER_F_GET VARcHAR2)
RETURN Table_Mnd
IS
V_OtherView Table_Mnd
BEGIN
SELECT CustomObjectTypeA (FromT.NUMBER_F, 
        FromT.OP_CODE, 
        FromT.CATG_CODE, 
        FromT.CATG_NAME, 
        FromT.CATG_SORT,
        FromT.ORG_CODE,
        FromT.ORG_NAME
        FromT.DATA_ENTRY_VALID, 
        FromT.NUMBER_RECEIVED, 
        FromT.YEAR_1, 
        FromT.YEAR_2)
BULK COLLECT INTO V_OtherView
FROM (SELECT NUMBER_F,
        OP_CODE,
        CATG_CODE,
        CATG_NAME,
        CATG_SORT,
        ORG_CODE,
        ORG_NAME
        DATA_ENTRY_VALID,
        NUMBER_RECEIVED,
        YEAR_1,
        YEAR_2, 
        ROW_NUMBER() OVER (PARTITION BY BY ORG_CODE ORDER BY NUMBER_RECEIVED DESC, LOAD_DATE DESC) AS ROW_NUMBER
      FROM V_ABC_123
      WHERE NUMBER_F = NUMBER_F_UPD AND DATA_ENTRY_VALID <> 'OnGoing'
     AND LOAD_DATE >= (SELECT sysdate-10 FROM dual)
     AND LOAD_DATE <= (SELECT DISTINCT LOAD_DATE 
               FROM V_ABC_123 
               WHERE NUMBER_RECEIVED = NUMBER_F_GET)) FromT
WHERE FromT.ROW_NUMBER=1;
RETURN V_OtherView;
END F_TABLE_APPROVED;

#2

There is a lot going on in that statement. Without seeing the data structure it would be difficult to imagine where the problem is.
If I was asked to investigate this I would try looking at each separate statement to see what is returned at each stage. The problem is likely in the complicated where clauses.


#3

I always want to see the latest record… records in my notes file.
I read to the end of file counting the number of lines in the file.
Then I reread it a 2nd time and start displaying the text 30 or so lines from the end.
Don’t worry about speed,… This plows through at 20,000,000 cps.