CODEWITHSUNDEEP
sqloracle-databaseto-char
Sol Lémond Guérin
Sol Lémond Guérin

Reputation: 21

Inserting a Date data

so I have this table

RPG_RETCON (
    UNIQUE_ID      VARCHAR2(100 BYTE),
    CONTAINER       VARCHAR2(100 BYTE),
    DATA_POINT_NAME VARCHAR2(100 BYTE),
    SOURCE_VALUE    VARCHAR2(100 BYTE),
    CSS_VALUE       VARCHAR2(100 BYTE),
    STATUS          VARCHAR2(100 BYTE)
)

And I I'm trying to insert this select statement into that table.

INSERT INTO RPG_RETCON
    (SELECT A.POOL_CUSIP_ID AS UNIQUE_ID,
                 '1_13_1C' AS CONTAINER,
                 'SECU_ACTL_STLM_DT' AS COLUMN_NAME1,
             TO_CHAR(A.SECU_ACTL_STLM_DT),
               TO_CHAR(B.SECU_ACTL_STLM_DT),
                 CASE
                    WHEN A.SECU_ACTL_STLM_DT = B.SECU_ACTL_STLM_DT
                    THEN
                       'PASS'
                    ELSE
                       'FAIL'
                 END
                    AS STATUS
            FROM POOL_1_13_1C_TRGT A
          LEFT JOIN POOL_1_13_1C_CSS B ON A.POOL_CUSIP_ID = B.POOL_CUSIP_ID);

Now the problem is that SECU_ACTL_STLM_DT is a date field and when I try to do the inserts, I get an invalid number error. If I take away the TO_CHAR to just A.SECU_ACTL_STLM_DT, B.SECU_ACTL_STLM_DT, I get invalid month.

Note: I absolutely cannot change 

SOURCE_VALUE    VARCHAR2(100 BYTE)
CSS_VALUE       VARCHAR2(100 BYTE)

-- Within the table structure... They need to be VARCHAR2 Data types.

Are there any suggestions to where I can insert this select statement error free?

Upvotes: 2

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think your code should work. However, I would list the columns explicitly and add date formats for the insert. Perhaps this will help:

INSERT INTO RPG_RETCON(UNIQUE_ID, CONTAINER, COLUMN_NAME1, SOURCE_VALUE, CSS_VALUE, STATUS)
    SELECT A.POOL_CUSIP_ID AS UNIQUE_ID, '1_13_1C' AS CONTAINER,
           'SECU_ACTL_STLM_DT' AS COLUMN_NAME1,
           TO_CHAR(A.SECU_ACTL_STLM_DT, 'YYYY-MM-DD'),
           TO_CHAR(B.SECU_ACTL_STLM_DT, 'YYYY-MM-DD'),
           (CASE WHEN A.SECU_ACTL_STLM_DT = B.SECU_ACTL_STLM_DT
                 THEN 'PASS'
                 ELSE 'FAIL'
            END) AS STATUS
    FROM POOL_1_13_1C_TRGT A LEFT JOIN
         POOL_1_13_1C_CSS B
         ON A.POOL_CUSIP_ID = B.POOL_CUSIP_ID;

It is possible that one of the SECU_ACTL_STLM_DT columns is not a date and the comparison is failing.

Upvotes: 1

Related Questions