How to correctly refer to the next row in the table using the LEAD function?

Question

Oracle

There is a PRODUCTINFO table with two columns: PRODUCTID (NUMBER) and ALLOWFROM (DATE). I need to insert a new ALLOWTO (DATE) row into a table that is equal to the next ALLOWFROM entry minus 1 day. If there is no next ALLOWFROM entry for a particular PRODUCTID, insert 01-01-1900.

I am using this script but the result is not correct.

SELECT PRODUCTID, ALLOWFROM,
    LEAD(ALLOWTO -1, 1, '01-01-1900') OVER (ORDER BY ALLOWFROM ASC)  AS ALLOWTO
    FROM PRODUCTINFO
    ORDER BY PRODUCTID, ALLOWFROM;

What am I doing wrong? Where is the mistake?

Thank you!

Answer 1

Issue resolved. Must use OVER (PARTITION BY ITEM_ID ORDER BY ALLOWFROM ASC)