user2470075 user2470075 - 3 months ago 20
SQL Question

Stuck in ORA-01789: query block has incorrect number of result columns

I don't see where the problem is.
It keeps showing ORA-01789: query block has incorrect number of result columns
Last thing I did was to add

substrb(TO_CHAR (TO_DATE ('20130601','YYYY-MM-DD')+LEVEL- 1, 'YYYYMMDD'), -4, 2) ||'월'AS MONTH


Following the the whole query. I've been stuck in this for 2 days now...
Can anyone help?

SELECT X.*
FROM
(
SELECT
(CASE
WHEN DAY.DAY = 'SUM' THEN DAY.DAY
ELSE TO_CHAR (TO_DATE (DAY.DAY, 'YYYY-MM-DD'), 'YYYYMMDD')
END
) AS DAY,
DAY.WEEK,
MNOT.SUM_STD_CNT AS MNOT_CNT,
RSC.MOVIE_500K AS MOVIE_500K_CNT,
RSC.MOVIE_1M AS MOVIE_1M_CNT,
RSC.MOVIE AS MOVIE_CNT,
RSC.EXAM AS EXAM_CNT,
RSC.WEB AS WEB_CNT,
RSC.IMG AS IMG_CNT,
RSC.INTERRAC AS INTERRACTIVE_CNT,
RSC.DOC AS DOC_CNT,
MNOT.SUM_STD_CNT + RSC.MOVIE_500K + RSC.MOVIE_1M + RSC.MOVIE + RSC.EXAM + RSC.WEB + RSC.IMG + RSC.INTERRAC + RSC.DOC TOT_CNT
FROM
/* DATES */



(SELECT
TO_CHAR (TO_DATE ('20130601','YYYY-MM-DD')+LEVEL- 1, 'YYYY.MM.DD') AS DAY
,ceil(
(
to_number(substrb(TO_CHAR (TO_DATE ('20130601','YYYY-MM-DD')+LEVEL- 1, 'YYYYMMDD'), -2, 2))
+ 7
- to_number(TO_CHAR(TO_DATE(TO_CHAR (TO_DATE ('20130601','YYYY-MM-DD')+LEVEL- 1, 'YYYYMMDD'),'YYYYMMDD'),'D'))
)/7
)|| ' WEEK' AS WEEK,

substrb(TO_CHAR (TO_DATE ('20130601','YYYY-MM-DD')+LEVEL- 1, 'YYYYMMDD'), -4, 2) ||'MONTH'AS MONTH

from DUAL

CONNECT BY TO_DATE('20130601', 'YYYY-MM-DD') + LEVEL - 1 <= TO_DATE('20130830', 'YYYY-MM-DD')
UNION ALL
SELECT 'SUM', '' FROM DUAL
) DAY LEFT OUTER JOIN
/* RESOURCE */
(
SELECT
NVL(DT_G.COMM_DT, 'SUM') COMM_DT
,NVL(SUM(DT_G.MOVIE_500K), 0) AS MOVIE_500K
,NVL(SUM(DT_G.MOVIE_1M), 0) AS MOVIE_1M
,NVL(SUM(DT_G.MOVIE), 0) AS MOVIE
,NVL(SUM(DT_G.EXAM), 0) AS EXAM
,NVL(SUM(DT_G.DOC), 0) AS DOC
,NVL(SUM(DT_G.IMG), 0) AS IMG
,NVL(SUM(DT_G.WEB), 0) AS WEB
,NVL(SUM(DT_G.INTERRAC), 0) AS INTERRAC
FROM
(
SELECT
COMM_DT
, CASE SUMT.RSC_TP_DSCD WHEN 'RTP10' THEN SUMT.SUM_500K END MOVIE_500K
, CASE SUMT.RSC_TP_DSCD WHEN 'RTP10' THEN SUMT.SUM_1M END AS MOVIE_1M
, CASE SUMT.RSC_TP_DSCD WHEN 'RTP10' THEN SUMT.SUM_500K+SUMT.SUM_1M END AS MOVIE
, CASE SUMT.RSC_TP_DSCD WHEN 'RTP11' THEN SUMT.SUM_STD_CNT END AS EXAM
, CASE SUMT.RSC_TP_DSCD WHEN 'RTP12' THEN SUMT.SUM_STD_CNT END AS DOC
, CASE SUMT.RSC_TP_DSCD WHEN 'RTP13' THEN SUMT.SUM_STD_CNT END AS IMG
, CASE SUMT.RSC_TP_DSCD WHEN 'RTP14' THEN SUMT.SUM_STD_CNT END AS WEB
, CASE SUMT.RSC_TP_DSCD WHEN 'RTP01' THEN SUMT.SUM_STD_CNT END AS INTERRAC
FROM (
SELECT RSC_TP_DSCD, SUM(STDY_CNT) AS SUM_STD_CNT, SUM(MOVIE_STDY_CNT_N1M) AS SUM_1M, SUM(MOVIE_STDY_CNT_N500K) AS SUM_500K, COMM_DT
FROM (
SELECT RSC_SNO, STDY_CNT, MOVIE_STDY_CNT_N1M, MOVIE_STDY_CNT_N500K, COMM_DT
FROM LRMS.V_EBSM_PKG_RSC_COMM_CNT
WHERE PKG_SNO = 0 AND RSC_SNO != 0
AND COMM_DT BETWEEN TO_CHAR(TO_DATE('20130501', 'YYYY-MM-DD'), 'YYYYMMDD') AND TO_CHAR(TO_DATE('20130530', 'YYYY-MM-DD'), 'YYYYMMDD')
) CNT
LEFT OUTER JOIN LRMS.V_LRRM_RSC RSC ON CNT.RSC_SNO = RSC.RSC_SNO
GROUP BY RSC_TP_DSCD, COMM_DT
) SUMT
) DT_G
GROUP BY ROLLUP(DT_G.COMM_DT)
) RSC ON DAY.DAY = RSC.COMM_DT

LEFT OUTER JOIN
(
SELECT NVL(SUM(STDY_CNT), 0) AS SUM_STD_CNT, NVL(COMM_DT, 'SUM') COMM_DT
FROM LRMS.V_EBSM_PKG_RSC_COMM_CNT
WHERE PKG_SNO != 0 AND RSC_SNO = 0
AND COMM_DT BETWEEN TO_CHAR(TO_DATE('20130601', 'YYYY-MM-DD'), 'YYYYMMDD') AND TO_CHAR(TO_DATE('20130830', 'YYYY-MM-DD'), 'YYYYMMDD')
GROUP BY ROLLUP(COMM_DT)
) MNOT ON DAY.DAY = MNOT.COMM_DT
) X

WHERE 1=1
and X.TOT_CNT IS NOT NULL

Xie Xie
Answer

Its this subselect:

SELECT 
    TO_CHAR (TO_DATE ('20130601','YYYY-MM-DD')+LEVEL- 1, 'YYYY.MM.DD') AS DAY,
    ceil( ... ) || ' WEEK' AS WEEK,
    substrb( ... ) ||'MONTH'AS MONTH
from DUAL
CONNECT BY TO_DATE('20130601', 'YYYY-MM-DD') + LEVEL - 1  <=  TO_DATE('20130830', 'YYYY-MM-DD')
UNION ALL
SELECT 'SUM', '' FROM DUAL

In a UNION ALL you need the same number of items in the select lists for both selects. You added MONTH to the first so you need to add something to the second:

SELECT 
    TO_CHAR (TO_DATE ('20130601','YYYY-MM-DD')+LEVEL- 1, 'YYYY.MM.DD') AS DAY,
    ceil( ... ) || ' WEEK' AS WEEK,
    substrb( ... ) ||'MONTH'AS MONTH
from DUAL
CONNECT BY TO_DATE('20130601', 'YYYY-MM-DD') + LEVEL - 1  <=  TO_DATE('20130830', 'YYYY-MM-DD')
UNION ALL
SELECT 
    'SUM', /*DAY*/ 
    '',    /*WEEK*/
    ''     /*MONTH*/ 
FROM DUAL