Case causing ORA-01790: expression must have same datatype as corresponding expression error

Question

I'm getting a ORA-01790: expression must have same datatype as corresponding expression error message. Below is the sql I'm using. Is the CASE statement causing this issue? I'm new to sql and this is the first time I've tried using a UNION statement. I've tried a join instead of the UNION but any join I have tried causes the prior_amt field to be blank. Thanks for the help........

SELECT 
    pa.BUSINESS_UNIT as BUS_UNIT, 
    pa.DESCR AS DESCRIPT,
    pdr.DEPTID AS DEPTID, 
    pdr.ASSET_ID AS ASSET_NO, 
    pdr.ACCOUNT_AD AS ACCT_AD, 
    pdr.BOOK AS BOOK,

    MAX(CASE WHEN (pdr.FISCAL_YEAR =2014 AND pdr.ACCOUNTING_PERIOD =11) THEN  pdr.DEPR END) as CURRENT_AMT,
    MAX(CASE WHEN (pdr.FISCAL_YEAR =2104 AND pdr.ACCOUNTING_PERIOD =10) THEN pdr.DEPR  END) as PRIOR_AMT,

    '' AS ACCT_DE,
    '' AS JRNL_ID,
    '' AS JRNL_DT

    FROM PS_ASSET pa

    INNER JOIN PS_DEPR_RPT pdr 
    ON pa.ASSET_ID = pdr.ASSET_ID
    AND pa.BUSINESS_UNIT = pdr.BUSINESS_UNIT

    WHERE 
    pa.BUSINESS_UNIT='A0465'
    AND pdr.BOOK='PERFORM'
    AND ((pdr.FISCAL_YEAR=2014 AND pdr.ACCOUNTING_PERIOD=11) 
    OR (pdr.FISCAL_YEAR=2014 AND pdr.ACCOUNTING_PERIOD=10))

    group by
    pa.business_unit,
    pa.descr,
    pdr.deptid,
    pdr.asset_id,
    pdr.account_ad,
    pdr.book

UNION ALL

    select
    '' as BUS_UNT,
    '' AS DESCRIPT,
    '' AS DEPTID, 
    '' AS ACCT_AD, 
    '' AS BOOK,
    '' AS CURRENT_AMT,
    '' AS PRIOR_AMT,
    pdl.asset_id AS ASSET_NO,
    pdl.account AS ACCT_DE,
    pdl.journal_id AS JRNL_ID,
    pdl.journal_date AS JRNL_DT

    from ps_dist_ln pdl

    where
    book = 'PERFORM'
    and business_unit = 'A0465'
    and fiscal_year = 2014
    and accounting_period = 11
    and distribution_type = 'DE'

Sarchet

Answer 1

Your problem is that you've put the columns in a different order in each half of the union. The columns have to match up, in the same order, between the two halves. It's not to do with the CASE expression.

Also, where you've written 2104, it should probably be 2014.

Geophilous

Thank you for your help. I changed the 2104 to 2014. I also found BUS_UNT in the second part should have been BUS_UNIT. Other than that, I don't see any thing different in the column order. i've looked and looked. Can you point out the differences. Thanks. – Sarchet

Thanks............I'm an accountant and am being moved more into a programming role. I haven't had any formal training in sql, outside of a couple of 1 days classes at a local university; which didn't cover UNION statements. It's things like the columns must be in the same order, that I never knew before, that are tripping me up. – Sarchet

Thank you for this! I could NOT figure out why my UNION was giving this error. :) – Penrose

7 years later, thanks for this! I hate oracle sql -___- – Etra

Answer 2

I have a weird situation with this kind of error:

I got the ORA-01790: expression must have same datatype as corresponding expression error message at a specific line.

Example:

INSERT  INTO TABLE_TO_INSERT (
                     COL_FIELD_1
                    ,COL_FIELD_2
                    ,COL_FIELD_3
                    ,COL_FIELD_4
                    ,COL_FIELD_5
                    ,COL_FIELD_6
                    )
SELECT  1
       ,2
       ,'IMPORT'
       ,'EXPORT'
       ,COL_DEXPIRATION ---- > SQL Developer shows ORA-01790 at this error line.
       ,COL_DEXPIRATION
FROM SOURCE_TABLE_1
UNION 
SELECT  1
       ,2
       ,'LOADING'
       ,'SAVING'
       ,COL_DCONFIRMDATE 
       ,COL_DATTACHMENTDATE
FROM SOURCE_TABLE_2;

In my case, the error was in a SELECT statement with an UNION; but, when I checked further the root fo the error, the error was indeed by a mistmatch between data types of the columns used in the SELECT statement, but not in the red-highlighted line shown in SQL Developer, but rather in the next SELECT statement.

I made the changes and the stored procedured compiled successfully.

Example:

INSERT  INTO TABLE_TO_INSERT (
                     COL_FIELD_1
                    ,COL_FIELD_2
                    ,COL_FIELD_3
                    ,COL_FIELD_4
                    ,COL_FIELD_5
                    ,COL_FIELD_6
                    )
SELECT  1
       ,2
       ,'IMPORT'
       ,'EXPORT'
       ,COL_DEXPIRATION 
       ,COL_DEXPIRATION
FROM SOURCE_TABLE_1
UNION 
SELECT  1
       ,2
       ,'LOADING'
       ,'SAVING'
       ,CAST(COL_DCONFIRMDATE AS VARCHAR2 (500)) -- > The real ORA-01790 was at this line and this was the change made.
       ,CAST(COL_DATTACHMENTDATE VARCHAR2 (500)) -- > The real ORA-01790 was at this line and this was the change made.
FROM SOURCE_TABLE_2;

TL;DR: Oracle is weird, check the error beyond the hightlighted line(s) and keep your code clean and organized as possible.

Rafiq

Answer 3

I had an issue creating a table using cte with wrongfully casted null values, e.g.

select
  cast(null as varchar2(4000)) value1,
  cast(null as varchar2(4000)) value2 -- This has to be `cast(null as number)`,
                                      --   because it's used further down in
                                      --     a calculation
from ...

Coset

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Buttonhook