결과가 이해가 안가요.

각 행마다 실제 숫자로 계산식을 적어주세요.

SELECT TT, SUM(VAL)
  FROM ( SELECT CONNECT_BY_ROOT(LINE_NUM) AS TT,
                VAL
           FROM TEMP A
          CONNECT BY LINE_NUM IN ( PRIOR LINE0, PRIOR LINE1, PRIOR LINE2, PRIOR LINE3, PRIOR LINE4))
 GROUP BY TT
 ORDER BY TT;

2번행의 쿼리를 잘못 작성하였습니다.

 SELECT 20   AS LINE_NUM
       ,200  AS VAL
       ,30   AS LINE0
       ,40   AS LINE1
       ,50   AS LINE2
       ,60   AS LINE3
       ,NULL AS LINE4
   FROM DUAL

이렇게 되어야 합니다...

===============================================

안녕하세요.

계산식은 각행별로 VAL + LINE0 + LINE1 + LINE2 + LINE3 + LINE4 으로 되어야 합니다.

이때, LINE0~4 의 값은 LINE_NUM 의 값으로 LINE_NUM 에 해당하는 VAL 값을 가져와야 합니다.

EX) 2행의 경우, 해당행의 VAL(200) + LINE0(행번호 30=> VAL 300) + LINE0(행번호 40=> VAL 400)  + LINE0(행번호 50=> VAL 500)  + LINE0(행번호 60=> VAL 600) = 2000 의 값이 나와야 합니다.

추가로 질문하나 더 여쭈어 봅니다...

WITH TEMP AS (
 SELECT 10   AS LINE_NUM
       ,100  AS VAL
       ,20   AS LINE0
       ,'A'  AS CAL00
       ,NULL AS LINE1
       ,NULL AS CAL01
       ,NULL AS LINE2
       ,NULL AS CAL02
       ,NULL AS LINE3
       ,NULL AS CAL03
       ,NULL AS LINE4
       ,NULL AS CAL04
   FROM DUAL
 UNION ALL
 SELECT 20   AS LINE_NUM
       ,200  AS VAL
       ,30   AS LINE0
       ,'A'  AS CAL00
       ,40   AS LINE1
       ,'A'  AS CAL01
       ,50   AS LINE2
       ,'A'  AS CAL02
       ,60   AS LINE3
       ,'S'  AS CAL03
       ,NULL AS LINE4
       ,NULL AS CAL04
   FROM DUAL
 UNION ALL
 SELECT 30   AS LINE_NUM
       ,300  AS VAL
       ,NULL AS LINE0
       ,NULL AS CAL00
       ,NULL AS LINE1
       ,NULL AS CAL01
       ,NULL AS LINE2
       ,NULL AS CAL02
       ,NULL AS LINE3
       ,NULL AS CAL03
       ,NULL AS LINE4
       ,NULL AS CAL04
   FROM DUAL
 UNION ALL
 SELECT 40   AS LINE_NUM
       ,400  AS VAL
       ,NULL AS LINE0
       ,NULL AS CAL00
       ,NULL AS LINE1
       ,NULL AS CAL01
       ,NULL AS LINE2
       ,NULL AS CAL02
       ,NULL AS LINE3
       ,NULL AS CAL03
       ,NULL AS LINE4
       ,NULL AS CAL04
   FROM DUAL
 UNION ALL
 SELECT 50   AS LINE_NUM
       ,500  AS VAL
       ,NULL AS LINE0
       ,NULL AS CAL00
       ,NULL AS LINE1
       ,NULL AS CAL01
       ,NULL AS LINE2
       ,NULL AS CAL02
       ,NULL AS LINE3
       ,NULL AS CAL03
       ,NULL AS LINE4
       ,NULL AS CAL04
   FROM DUAL
 UNION ALL
 SELECT 60   AS LINE_NUM
       ,600  AS VAL
       ,NULL AS LINE0
       ,NULL AS CAL00
       ,NULL AS LINE1
       ,NULL AS CAL01
       ,NULL AS LINE2
       ,NULL AS CAL02
       ,NULL AS LINE3
       ,NULL AS CAL03
       ,NULL AS LINE4
       ,NULL AS CAL04
   FROM DUAL
 )
SELECT *
 FROM TEMP A

================================

위와 같은 데이터가 있습니다.

각행별의 계산식입니다. VAL + (CAL00)LINE0 + (CAL01)LINE1 + (CAL02)LINE2 + (CAL03)LINE3 + (CAL04)LINE4

CAL00~04 컬럼은 'A'인경우 + 부호에 해당하고, 'S'인경우, - 부호입니다.

따라서, 2행을 예로 들면,

200 + (+)300[3행의 VAL] + (+)400[4행의 VAL] + (+)500[5행의 VAL] + (-)600[6행의 VAL] = 800

이런식으로

최종 결과값은

LINE_NUM   VAL
10         900
20         800
30         300
40         400
50         500
60         600

이렇게 나와야 됩니다.

계층구조(부모-자식 관계) 형태로 자료를 변경한뒤
하위에서 상위로 부호를 적용하여 누적합계 구하는 방법을 사용했습니다.
제시하신 예제에선 마이너스 부호가 한번만 나와서
마이너스가 중첩되는 경우에 대한 예제로 바꿔봤습니다.
아래는 이와 관련된 SQL퀴즈입니다.
http://www.gurubee.net/lecture/2643
http://www.gurubee.net/article/46097
 

WITH temp
(line_num, val, line0, cal00, line1, cal01, line2, cal02, line3, cal03, line4, cal04)
AS
(
SELECT 10, 100,   20,  'A', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM dual UNION ALL
SELECT 20, 200,   30,  'A',   40,  'A',   50,  'A',   60,  'S', NULL, NULL FROM dual UNION ALL
SELECT 30, 300, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM dual UNION ALL
SELECT 40, 400, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM dual UNION ALL
SELECT 50, 500, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM dual UNION ALL
--SELECT 60, 600, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM dual UNION ALL
SELECT 60, 600,   70,  'S', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM dual UNION ALL
SELECT 70, 700, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM dual
)
, tmp AS
(
SELECT c, p, s, b.val v
  FROM (SELECT DECODE(lv, -1, NULL, line_num) p
             , DECODE(lv, -1, line_num
                        , 0, line0, 1, line1, 2, line2, 3, line3, 4, line4) c
             , DECODE(lv, -1, 'A'
                        , 0, cal00, 1, cal01, 2, cal02, 3, cal03, 4, cal04) s
          FROM temp
             , (SELECT LEVEL-2 lv FROM dual CONNECT BY LEVEL <= 6)
        ) a
     , temp b
 WHERE a.c = b.line_num
)
SELECT line_num
     , SUM(v * s) v
  FROM (SELECT CONNECT_BY_ROOT(c) line_num
             , DECODE(
               MOD(LENGTH(REPLACE(SYS_CONNECT_BY_PATH(s, ','), 'S')), 2)
               , 0, 1, -1) s
             , v
             , c, p
          FROM tmp
         START WITH p IS NULL
         CONNECT BY PRIOR c = p
        )
 GROUP BY line_num
 ORDER BY line_num
;

SELECT TT, SUM(VAL)
  FROM ( SELECT CONNECT_BY_ROOT(LINE_NUM) AS TT,
                VAL * DECODE(DECODE(LINE_NUM, PRIOR LINE0, PRIOR CAL00, PRIOR LINE1, PRIOR CAL01, PRIOR LINE2, PRIOR CAL02, PRIOR LINE3, PRIOR CAL03), 'S', -1, 1) AS VAL
           FROM TEMP A
          CONNECT BY LINE_NUM IN ( PRIOR LINE0, PRIOR LINE1, PRIOR LINE2, PRIOR LINE3, PRIOR LINE4))
 GROUP BY TT
 ORDER BY TT;

예 다시보니 이해가 되네요

설명 감사 드립니다.. ^_^;;