I am trying to populate an oracle table from an interface table
here is the table design
Source Table:
CREATE TABLE USAGE_INTERFACE
(
"MTRID" CHAR(10 BYTE),
"D1\_READ\_SEQ" NUMBER(5,0),
"SEC\_PER\_INTRVL" NUMBER(5,0),
"UOM" VARCHAR2(30 BYTE),
"D1\_TOU\_CD" VARCHAR2(30 BYTE),
"D1\_SQI\_CD" VARCHAR2(30 BYTE),
"MTRSN" VARCHAR2(20 BYTE),
"DATE\_TIME" DATE,
"USAGE" NUMBER(13,6),
"READ\_FREQ" VARCHAR2(10 BYTE)
)
Destination Table:
CREATE TABLE METER_USAGE_DATA_NCIS
(
"MTRID" VARCHAR2(254 BYTE),
"UOM" VARCHAR2(30 BYTE),
"MTRSN" VARCHAR2(20 BYTE),
"DATE\_TIME" DATE,
"FIRST\_DATE\_TIME" VARCHAR2(20 BYTE),
"USAGE" NUMBER(13,6),
"TIME\_OF\_USE" VARCHAR2(12 BYTE),
"READ\_FREQ" VARCHAR2(20 BYTE),
"CREATION\_DATE" DATE DEFAULT sysdate
)
the issue is when I try inserting the records the time component gets stripped ... I did used the length function on the date_time column it shows as 20 in the source table it should be 19 so I used a TRIM
**Source Table data:**time component is showing properly
**Destination Table DATA: time component is showing as all zeros
**
INSERT INTO METER_USAGE_DATA_NCIS (MTRID,
UOM,
MTRSN,
DATE\_TIME,
USAGE,
READ\_FREQ)
SELECT DISTINCT
TRIM (TO\_CHAR (TRIM (REPLACE (ue.MTRID, CHR (13))))),
TRIM (ue.UOM),
TRIM (ue.MTRSN),
TRIM(ue.DATE\_TIME)
TRIM (ue.USAGE),
TRIM (ue.READ\_FREQ)
FROM usage\_interface ue
WHERE TRUNC(UE.DATE\_TIME) > ( SELECT TRUNC(max(s\_DATETIME)) FROM SUMMARY\_QTRHOUR\_NCIS )
AND trim(d1\_sqi\_cd) is null;
COMMIT;