Home » Developer & Programmer » Forms » ORA-01841 error.... (oracle)
| ORA-01841 error.... [message #588330] |
Tue, 25 June 2013 01:47  |
 |
hashimaro
Messages: 1
Registered: June 2013
|
Junior Member |
|
|
SELECT X.*
FROM
(
SELECT
(CASE
WHEN DAY.DAY = 'TOTAL' 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
/* DATE */
(SELECT
TO_CHAR (TO_DATE ('20130501','YYYY-MM-DD')+LEVEL- 1, 'YYYYMMDD') AS DAY
,CEIL(
(
to_number(SUBSTRB(TO_CHAR (TO_DATE ('20130501','YYYY-MM-DD')+LEVEL- 1, 'YYYYMMDD'), -2, 2))
+ 7
- to_number(TO_CHAR(TO_DATE(TO_CHAR (TO_DATE ('20130501','YYYY-MM-DD')+LEVEL- 1, 'YYYYMMDD'),'YYYYMMDD'),'D'))
)/7
)|| ' 주차' WEEK
FROM DUAL
CONNECT BY TO_DATE('20130501', 'YYYY-MM-DD') + LEVEL - 1 <![CDATA[<=]]> TO_DATE('20130530', 'YYYY-MM-DD')
UNION ALL
SELECT 'TOTAL', '' FROM DUAL
) DAY LEFT OUTER JOIN
/* RESOURCE */
(
SELECT
NVL(DT_G.COMM_DT, 'ALL') 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
/* MNOTE */
LEFT OUTER JOIN
(
SELECT NVL(SUM(STDY_CNT), 0) AS SUM_STD_CNT, NVL(COMM_DT, 'ALL') 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')
GROUP BY ROLLUP(COMM_DT)
) MNOT ON DAY.DAY = MNOT.COMM_DT
) X
WHERE 1=1
and X.TOT_CNT IS NOT NULL
<isNotEmpty property="startDt" prepend="AND">
<isNotEmpty property="endDt">
#startDt# <![CDATA[<=]]> TO_CHAR(TO_DATE(DAY, 'YYYY-MM-DD'), 'YYYYMMDD') AND #endDt# <![CDATA[>=]]> TO_CHAR(TO_DATE(DAY, 'YYYY-MM-DD'), 'YYYYMMDD')
</isNotEmpty>
</isNotEmpty>
When I execute this query in Toad, I am getting
org.springframework.dao.DataIntegrityViolationException: SqlMapClient operation; SQL [];
--- The error occurred while applying a parameter map.
--- Check the ContentStatis.statis-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLDataException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0.
; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the ContentStatis.statis-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLDataException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0.
What is the prbloblem?
|
|
|
|
| Re: ORA-01841 error.... [message #588331 is a reply to message #588330] |
Tue, 25 June 2013 01:54  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
*Cause: Illegal year entered
*Action: Input year in the specified range
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 05:25:17 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
