SQL 导致 ORA-01790 的情况：表达式必须与相应的表达式错误具有相同的数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25797019/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Case causing ORA-01790: expression must have same datatype as corresponding expression error
提问by Shaves
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........
我收到一个 ORA-01790: 表达式必须与相应的表达式错误消息具有相同的数据类型。下面是我正在使用的sql。CASE 语句是否导致此问题？我是 sql 新手，这是我第一次尝试使用 UNION 语句。我尝试过连接而不是 UNION，但是我尝试过的任何连接都会导致prior_amt 字段为空。谢谢您的帮助........
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'
回答by Dawood ibn Kareem
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.
您的问题是您在联合的每一半中以不同的顺序放置了列。列必须以相同的顺序在两半之间匹配。这与 CASE 表达式无关。
Also, where you've written 2104, it should probably be 2014.
另外，你写 2104 的地方，应该是 2014 年。
