关闭

ORA-00918 column ambiguously defined

标签: oraclesql数据库
756人阅读 评论(1) 收藏 举报
分类:
11.2.0.1中同样的一个SQL能正常执行:
 select  -- 代金券收入
         'VOUCHER' AS SUMMODE,
          a.billingdate,
          a.rdate,
          a.storeroomid,
       a.accountscode,
       g.accountsname,
       I.RELATED,--业务内容
       CASE WHEN I.RELATED = 'HQ_billcheckout' THEN H.DICTIONARYCODE ELSE NULL END AS PAYMENTCODE,
       CASE WHEN I.RELATED = 'HQ_billcheckout' THEN H.DICTIONARYNAME ELSE NULL END AS PAYMENT,--收款方式名称
       CASE WHEN I.RELATED = 'HQ_billcheckout' THEN h.statistic_class ELSE NULL END AS statistic_class,  --收款方式分类
       nvl(a.debit,0)-nvl(a.credit,0) as tradeMoney,
       0 as StandDealTotalm,
       0 as DiscDealTotal,
       0 as OtheDealTotal
  from HQ_accountingbooks a
  inner join m_coupon_grant d on a.bid=nvl(d.bid,d.cgid)
  inner join HQ_accounts g on a.accountscode=g.accountscode
  LEFT JOIN (SELECT A1.* FROM HQ_DATADICTIONARY A1 WHERE A1.DICTIONARYTYPE=1) H ON g.ACCOUNTSCODE=H.ACCOUNTCODE
  inner join HQ_billcase_accounts i on d.billsubcase=i.billsubcase and a.accountscode=i.accountscode
where BILLSUBCASE = 5201;
在11.2.0.4中报错:
ORA-00918: column ambiguously defined
对比了下,两个数据库中所有对应的表表结构都一样,为什么在11.2.0.1中能正常执行,在11.2.0.4中执行不了?
发现这个SQL中有2个表中都有BILLSUBCASE这一列:
select BILLSUBCASE from HQ_accountingbooks;
select BILLSUBCASE from m_coupon_grant;
于是指定a.BILLSUBCASE = 5201;即解决。
看来11.2.0.4中对SQL的语法要求更严格。
1
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:153218次
    • 积分:2688
    • 等级:
    • 排名:第14089名
    • 原创:111篇
    • 转载:14篇
    • 译文:1篇
    • 评论:16条
    文章分类
    最新评论