oracle sql to copy rows through to down below, excel like coping

Question

http://pastebin.com/m24c4f508

--any help would be appreciated

Answer 1

If you just want to get status from the previous row if it is NULL, use LAG:

SELECT
  rn,
  device_num,
  COALESCE( status_01, LAG(status_01) OVER ( ORDER BY pri_id ) ) status_01,
  COALESCE( status_02, LAG(status_02) OVER ( ORDER BY pri_id ) ) status_02
FROM tt1
ORDER BY pri_id

Your example takes it from the last line where it was set, so you might need to do something like that:

SELECT
  rn,
  device_num,
  COALESCE( status_01, ( SELECT MAX(status_01) KEEP( DENSE_RANK LAST ORDER BY pri_id )
                         FROM tt1 tt1_1
                         WHERE tt1_1.id < tt1.id
                         AND tt1_1.status_01 IS NOT NULL ) ) status_01,
  COALESCE( status_02, ( SELECT MAX(status_02) KEEP( DENSE_RANK LAST ORDER BY pri_id )
                         FROM tt1 tt1_2
                         WHERE tt1_2.id < tt1.id
                         AND tt1_2.status_02 IS NOT NULL ) ) status_02
FROM tt1
ORDER BY pri_id