Datatype Error ORA-01790

Question

I have an error with datatype:

ORA-01790: expression must have same datatype as corresponding expression

I already check all the datatype same already.

Here is my code:

SELECT CONTRACT_ID, DATEKEY, 'Projection', '1', OPEN_BALANCE,
  PRINC_REPAYMENT, INTEREST, PAR_PREPAYMENT, FULL_REDEMPTION, REDRAW_AMOUNT, PRIN_DEFAULTS,
  PRIN_RECOVERY, PRIN_DEL, INTE_DEL, DEL_PRIN_COLL, DEL_INT_COLL, CLOSE_PRIN_BAL, EXT_ID,
  LAS_ACTIVE_FLG, PROD_ID, ASSET_TYP_ID, CPT_ID, ACCRETION, CASHFLOW_DT 
FROM FACT_PWOR_CASHFLOW
WHERE ACTUAL_FLG = 'Y' AND LATEST_PROJ_IND = '0' AND LAS_ACTIVE_FLG = 'Y'
UNION
(SELECT CONTRACT_ID, DATEKEY_COLL, 'Actual', '1', SUM(OPENING_BALANCE) SUM_OPEN_BAL,
    SUM(MI_PRINCIPLE) SUM_MI_PRIN, SUM(MI_INTEREST) SUM_MI_INTER, SUM(PARTIAL_PREPAYMENT) SUM_PAR_PREPAY,
    SUM(FULL_REDEMPTION) SUM_FULL_REDEM, SUM(TTL_AMT_REDRAW) SUM_TTL_AMT,
    '0', '0', '0', '0', '0', '0', SUM(CLOSING_BALANCE) SUM_CLOS_BAL, '0', 'Y',
    PROD_ID, ASSET_TYPE_ID, CPT_ID, '0', COLLECTION_START_DT
FROM FACT_PWOR_COLLECTION
WHERE LAS_ACTIVE_FLG = 'Y'
GROUP BY CONTRACT_ID, DATEKEY_COLL, PROD_ID, ASSET_TYPE_ID, CPT_ID, COLLECTION_START_DT);

When I run this query, it gave me this error "ORA-01790: expression must have same datatype as corresponding expression". I already check all the datatype match already. — Benny Gold
I would suggest you also add some sample data with the tables design, it would help us more to help you. — Paul Karam
Possible duplicate of How to resolve Oracle error ORA-01790? — Paul Karam

Barbaros Özhan Barbaros Özhan · Accepted Answer · 2018-03-29T06:25:58

When you use a set operation like UNION in your case, all the matching columns' data type in both tables FACT_PWOR_CASHFLOW and FACT_PWOR_COLLECTION must match.

As an example, If data type of column ACCRETION in the first SELECT statement may be integer, while the corresponding column which is stated as '0' in the second SELECT statement is of string type , then DataType Error occurs.

Remove all columns inthe SELECT lists and then Try to add columns for both SELECT statements step by step by starting from the first one CONTRACT_ID.

Datatype Error ORA-01790

1 Answers