Here's my script:
INSERT INTO BRAND_RECORD SELECT STRENGTH, GENERIC_NAME, TO_DATE(a1.FIRST_CLAIM_DATE) as INTERCHANGEABILITY_DATE FROM DRUG_INFO INNER JOIN ( SELECT FIRST_CLAIM_DATE, SEQUENTIAL_ID FROM DRUG_INFO WHERE SEQUENTIAL_ID IN ( SELECT MIN(SEQUENTIAL_ID) FROM ( SELECT * FROM DRUG_INFO WHERE SEQUENTIAL_ID NOT IN ( SELECT MIN(SEQUENTIAL_ID) FROM DRUG_INFO GROUP BY GENERIC_NAME, STRENGTH ) ) GROUP BY GENERIC_NAME, STRENGTH ) ) a1 ON (a1.SEQUENTIAL_ID = DRUG_INFO.SEQUENTIAL_ID + 1);
Summary: Everything after
INSERT INTO BRAND_RECORD works perfectly well. It is a query that produces a
STRENGTH, GENERIC_NAME, and
BRAND_RECORD is a table with the columns
STRENGTH, GENERIC_NAME, INTERCHANGEABILITY_DATE.
However, I get the following error:
Error report - SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0" *Cause: Illegal year entered *Action: Input year in the specified range
This seems like some sort of conversion problem. But I'm selecting a date and putting it into a table that takes a date! Any idea what the problem might be?
UPDATE: I changed my default date format to MM/DD/YYYY HH24:MI:SS. I now get the following error when I try this:
Error report - SQL Error: ORA-01858: a non-numeric character was found where a numeric was expected 01858. 00000 - "a non-numeric character was found where a numeric was expected" *Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model. *Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation. Error starting at line : 1 in command - INSERT INTO BRAND_RECORD Error at Command Line : 1 Column : 24 Error report - SQL Error: ORA-00926: missing VALUES keyword 00926. 00000 - "missing VALUES keyword" *Cause: *Action:
Here are my create table statements for both tables:
CREATE TABLE BRAND_RECORD ( GENERIC_NAME VARCHAR2(60 BYTE) NOT NULL , INTERCHANGEABILITY_DATE DATE , STRENGTH VARCHAR2(25 BYTE) ) LOGGING TABLESPACE DATAL03 PCTFREE 10 INITRANS 1 STORAGE ( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT ) NOCOMPRESS NOPARALLEL; CREATE TABLE DRUG_INFO ( DIN VARCHAR2(20 BYTE) NOT NULL , TRADE_NAME VARCHAR2(50 BYTE) NOT NULL , FORM VARCHAR2(40 BYTE) , STRENGTH VARCHAR2(25 BYTE) , GENERIC_NAME VARCHAR2(60 BYTE) NOT NULL , MANUFACTURER VARCHAR2(100 BYTE) NOT NULL , CR_DATE DATE , NOC_DATE DATE , FIRST_CLAIM_DATE DATE , SEQUENTIAL_ID NUMBER NOT NULL ) LOGGING TABLESPACE DATAL03 PCTFREE 10 INITRANS 1 STORAGE ( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT ) NOCOMPRESS NOPARALLEL;