Hello All,
I have sample data as below and it's present in order in the sample table (let us not worry how it's ordered) and there can be some rows in last with 0 value i.e. second last or maybe third last and so on .. and the ultimate last rows with NULL data and some value. The table sample_value table also holds a unique value across rows for each category stored in the sample table. The sample table rows are the multiple of the category value of the sample value table in order. Now, the requirement is to replace the 0 value in the sample table with category value i.e. 0 by 7 when the sample last row value is greater than the sample data category value.
CREATE TABLE sample AS
( SELECT 'A' AS data
, 1 AS category
, 7 AS value
, 'Y' AS flag
FROM dual UNION ALL
SELECT 'B' AS data
, 1 AS category
, 14 AS value
, 'Y' AS flag
FROM dual UNION ALL
SELECT 'C' AS data
, 1 AS category
, 7 AS value
, 'Y' AS flag
FROM dual UNION ALL
SELECT 'D' AS data
, 1 AS category
, 0 AS value
, 'Y' AS flag
FROM dual UNION ALL
SELECT NULL AS data
, 1 AS category
, 8 AS value
, 'N' AS flag
FROM dual );
CREATE TABLE sample_value AS
( SELECT 1 AS category
, 7 AS category_value
FROM dual );