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 I understand what you want to do correctly (if there is no value for status, use the one from previous line) you should look into using LAG and LEAD combined with an NVL/COALESCE

Answer 2

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