关于dblink

shenjinchun

create  PUBLIC  database link cbsdblink
  connect to Tcheck identified by Tcheck
  using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ODS)
      (INSTANCE_NAME = cisg2)
    )
  )';

select * from fccont@cbsdblink a left join fcpol@cbsdblink b on a.innercontno = b.innercontno
                                  and b.agentcode not in  (select t.CalBatchNo from FSInsurePrem@cbsdblink t)

报这个错误
ORA-00904: "A"."INNERCONTNO": invalid identifier
ORA-02063: 紧接着 line (起自 CBSDBLINK)

select a.*
   from fccont@cbsdblink  a
   left join fcpol@cbsdblink  b on b.innercontno = a.innercontno
                    and b.innercontno not in
                        ('14', '52', '54', '55')

select * from fccont@cbsdblink a

select t.CalBatchNo from FSInsurePrem@cbsdblink

上面三句sql查都是没有问题的,不知是什么原因，但总觉得很有可能与dblink的配置有关,望各位大侠帮帮忙？

blue_stone

select * from fccont@cbsdblink a left join fcpol@cbsdblink b on a.innercontno = b.innercontno
                                  and b.agentcode not in  (select t.CalBatchNo from FSInsurePrem@cbsdblink t)

中的select * 是否有岐义? 因为a和b中都有innercontno

试一下

select a.* from fccont@cbsdblink a left join fcpol@cbsdblink b on a.innercontno = b.innercontno
                                  and b.agentcode not in  (select t.CalBatchNo from FSInsurePrem@cbsdblink t)