1

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 INTERCHANGEABILITY_DATE. 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;

closed as off-topic by Paul White, Jack Douglas May 6 '15 at 19:24

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, Jack Douglas
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    If you are selecting a date then why do you have TO_DATE(a1.FIRST_CLAIM_DATE) ? Seems to me that a1.FIRST_CLAIM_DATE is actually of a character type, and you're trying to do a conversion to a date. Since you didn't specify a date format, the default is used, and you got into trouble because your "dates" are not formatted that way. Specify the date format in to_date() function explicitly. – zgguy May 6 '15 at 17:38
  • That TO_DATE was an attempt to fix the problem. FIRST_CLAIM_DATE is without a doubt a date type. Maybe the problem is that one table holds dates in a different format? How do I change that on a per-table basis? How would I even see? – Daniel Paczuski Bak May 6 '15 at 17:39
  • Did you try to manually inspect the data you get from the select? Can you post DRUG_INFO create table statement? – zgguy May 6 '15 at 17:43
  • I have manually inspected them. I posted both of the create table statements above. – Daniel Paczuski Bak May 6 '15 at 17:45
  • 1
    That worked! Weird! Do you have any idea why that worked? – Daniel Paczuski Bak May 6 '15 at 17:54
2

Zgguy answered this in comments:

Explicitly stating columns in the insert solved the problem, as it was trying to put columns in the wrong columns.

INSERT INTO BRAND_RECORD (STRENGTH, GENERIC_NAME, INTERCHANGEABILITY_DATE)
  • 2
    How did you expect Oracle to know what columns were the 'right' columns if you didn't make it explicit? You should always, always, include the column names in an insert. – Jack Douglas May 6 '15 at 19:26
  • Yeah I was being a little dumb. I figured SQL would automatically match columns with the same names. – Daniel Paczuski Bak May 7 '15 at 16:03
  • Ah, I see. That would be a reasonable enough thing to do (except typos would result in exceedingly hard to find bugs) but no RDBMS I know of does that. Much better would be for an error to be returned unless columns are specified - that'd prevent all sorts of issues like this. – Jack Douglas May 7 '15 at 16:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.