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;