아래와 같이 하면 4건이 나옵니다. 총 rownum 구간은 14인데 4건에 아래는 null Row를 해서 총 Count(*)는 14건이 나오고 싶은데 방법이 없을까요?
밑에 29~~42 사이입니다.
SELECT * FROM (
SELECT ITEM_NAME,ITEM_ID , ITEM_VALUE, ROUND(WEEK_AVG,3) AS WEEK_AVG, ROUND(MONTH_AVG,3) AS MONTH_AVG, ROUND(YEAR_AVG,3) AS YEAR_AVG, ETC, CHARTGRAPH , ROWNUM AS RNUM FROM (
SELECT TRIM(ITEM_NAME) AS ITEM_NAME , TRIM(ITEM_ID) AS ITEM_ID , SUM(ITEM_VALUE) AS ITEM_VALUE, SUM(WEEK_AVG) AS WEEK_AVG, SUM(MONTH_AVG) AS MONTH_AVG, SUM(YEAR_AVG) AS YEAR_AVG, SUM(ETC) AS ETC, CHARTGRAPH FROM (
SELECT A.ITEM_NAME, 0 ITEM_VALUE, B.ITEM_ID, 0 AS WEEK_AVG, 0 AS MONTH_AVG, AVG(DECODE(ITEM_VALUE,-999,0,ITEM_VALUE)) AS YEAR_AVG, 0 ETC,'' AS CHARTGRAPH
FROM W_ITEMINFO_TB A ,
W_DAYDATA_TB B
WHERE A.ITEM_ID = B.ITEM_ID
AND B.ITEM_ID IN('W01','W66')
AND SUBSTR(B.MSR_DATE, 1,4) = TO_CHAR(SYSDATE ,'YYYY')
GROUP BY A.ITEM_NAME , B.ITEM_ID
UNION ALL
SELECT A.ITEM_NAME , 0 ITEM_VALUE , B.ITEM_ID , 0 AS WEEK_AVG , AVG(DECODE(ITEM_VALUE,-999,0,ITEM_VALUE)) AS MONTH_AVG , 0 AS YEAR_AVG ,0 ETC , '' AS CHARTGRAPH
FROM W_ITEMINFO_TB A ,
W_DAYDATA_TB B
WHERE A.ITEM_ID = B.ITEM_ID
AND B.ITEM_ID IN('W01','W66')
AND SUBSTR(B.MSR_DATE, 1,8) BETWEEN TO_CHAR(SYSDATE -31,'YYYYMMDD') AND TO_CHAR(SYSDATE ,'YYYYMMDD')
GROUP BY A.ITEM_NAME , B.ITEM_ID
UNION ALL
SELECT A.ITEM_NAME , 0 ITEM_VALUE , B.ITEM_ID , AVG(DECODE(ITEM_VALUE,-999,0,ITEM_VALUE)) AS WEEK_AVG , 0 AS MONTH_AVG , 0 AS YEAR_AVG , 0 ETC , '' AS CHARTGRAPH
FROM W_ITEMINFO_TB A ,
W_DAYDATA_TB B
WHERE A.ITEM_ID = B.ITEM_ID
AND B.ITEM_ID IN('W01','W66')
AND SUBSTR(B.MSR_DATE, 1,8) BETWEEN TO_CHAR(SYSDATE-7,'YYYYMMDD') AND TO_CHAR(SYSDATE,'YYYYMMDD')
GROUP BY A.ITEM_NAME, B.ITEM_ID
UNION ALL
SELECT A.ITEM_NAME , B.ITEM_VALUE , B.ITEM_ID , 0 AS WEEK_AVG , 0 AS MONTH_AVG , 0 AS YEAR_AVG , B.ETC , '' AS CHARTGRAPH
FROM W_ITEMINFO_TB A ,
W_DAYDATA_TB B
WHERE A.ITEM_ID = B.ITEM_ID
AND B.ITEM_ID IN('W01','W66')
AND SUBSTR(MSR_DATE, 1,8) = TO_CHAR(SYSDATE,'YYYYMMDD')
)
GROUP BY ITEM_NAME ,ITEM_ID ,CHARTGRAPH)
) A
, (select rownum from dual connect by rownum <= 14)
WHERE A.RNUM BETWEEN 29 AND 42
|