My Oracle Support Banner

Wrong data inserted by insert into select with to_char(sysdate) (Doc ID 2797117.1)

Last updated on AUGUST 11, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 19.1.0.0.0 and later
Information in this document applies to any platform.

Symptoms

When executing insert into select with to_char(sysdate) as following SQL, wrong data inserted.

insert into TAB2
SELECT COL1 ,COL2 ,COL3 ,COL4 ,COL5 ,COL6 ,COL7 ,COL8 ,SUM(COL9) ,SUM(COL10) ,SUM(COL11)
FROM (
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 1) ,'YYYY') ||S.C5 COL1
,TO_CHAR(ADD_MONTHS(SYSDATE, 1) ,'YYYYMM') COL2
,TO_CHAR(ADD_MONTHS(SYSDATE, 1) ,'YYYY') COL3
,SUBSTR(S.C5, 1, 2) AS COL4
,SUBSTR(S.C5, 3, 2) AS COL5
,S.C1 AS COL6
,SHU.A2 AS COL7
,CASE WHEN S.C3 ='00' THEN '01' WHEN S.C1 ='06'
AND S.C3 = '28'THEN '01'WHEN S.C1 = '17'THEN '01'WHEN S.C1 = '82'
AND S.C3 IN('22', '25')THEN '01'ELSE S.C3 END COL8 , 0 AS COL9 ,
CASE WHEN S.C6 = '-'THEN S.C7 *(- 1)ELSE S.C7 END AS COL10 ,
CASE WHEN S.C8 = '-'THEN S.C9 *(- 1)ELSE S.C9 END AS COL11
FROM TAB3 S ,VW1 SHU
WHERE S.C4 = SHU.A1
AND S.C2 <>'C'
AND ((S.C2 = 'A' AND S.C3 = '00')
OR (S.C2 = 'B' AND S.C3 <>'99')))
GROUP BY COL1 ,COL2 ,COL3 ,COL4 ,COL5 ,COL6 ,COL7 ,COL8;

 

Correct data
COL1 COL2 COL3
------ ---- ----
202107 2021 06
202107 2021 06
202107 2021 06
202107 2021 06
202107 2021 06
202107 2021 06

Wrong data
COL1 COL2 COL3
------ ---- ----
? 1061 06
? 1061 06
? 1061 06
0614? 2107 06
0614? 2107 06
0614? 2107 06

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.