CREATE OR REPLACE VIEW CONTRACT AS select t.*, a.trade_id from (select contract.*,compact.agent_type_id,compact.lastreceivedate,compact.v_cont_banben,compact.othercost,compact.averagediscount,compact.adcontent,compact.walla_id ,compact.special_list_id from contract @nfadv,compact @nfadv where compact.compact_id = contract.compact_id) t, (select tc.contract_no,tc.trade_id from (select contract_no,trade_id,row_number() over (partition by contract_no order by trade_id) as row_number from consch_list@nfadv) tc where tc.row_number = 1)b where t.contract_no(+) = b.contract_no你删除掉。。用这个办法试一下应该是可以了。。
没有看到a2这个别名呀。图和你的sql是一致的么。
这个view编译通过了吗?别名不能用row_number的,编译通不过才是。
可以用的,我编译通过了,如果用select * from contract 的话是没问题的,查得出正确数据
select t.*, a.trade_id
from
(select contract.*,compact.agent_type_id,compact.lastreceivedate,compact.v_cont_banben,compact.othercost,compact.averagediscount,compact.adcontent,compact.walla_id ,compact.special_list_id from contract @nfadv,compact @nfadv where compact.compact_id = contract.compact_id) t,
(select tc.contract_no,tc.trade_id from (select contract_no,trade_id,row_number() over (partition by contract_no order by trade_id) as row_number from consch_list@nfadv) tc where tc.row_number = 1)b
where t.contract_no(+) = b.contract_no你删除掉。。用这个办法试一下应该是可以了。。