gpt4 book ai didi

sql - 引起ORA-01790的案例: expression must have same datatype as corresponding expression error

转载 作者:行者123 更新时间:2023-12-04 10:00:05 31 4
gpt4 key购买 nike

我收到了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'

最佳答案

您的问题是您在联合的每一半中都以不同的顺序放置了列。列必须以相同的顺序在两个半部分之间匹配。这与CASE表达式无关。

另外,在您编写2104的位置,可能应该是2014年。

关于sql - 引起ORA-01790的案例: expression must have same datatype as corresponding expression error,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/25797019/

31 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com