Search code examples
sqloracleformsoraclereports

If any column has NULL value in oracle forms then how the next row to be inserted

If any column has NULL value in oracle forms then how the next row to be inserted.

empID   city
1001    NYC
1002    DC
1003    CA
1004    NULL
1005    LA
1006    PL

Here in 4th column NULL value is there in Oracle forms.If we insert the next value then it revert back to 4th row means LA shift to 4th row & PL shift to 5th row.How can we done it that inserted rows shift up in Oracle forms?

Solution

  • You can do this using lag( . . . ignore nulls):

    select empID,
       coalesce(city, lag(city ignore nulls) over (order by empID)) as city
from t;

    If you actually want an update, you can use a correlated subquery:

    update t
    set city = (select max(t2.city) keep (dense_rank first order by t2.empID desc)
                from t t2
                where t2.empID < t.empId
               )
    where city is null;