报错ora00933:sql命令不正确结束,好像inner join有问题,以下是生产库部分代码,表:bill_master.bill_no,id_no,name,visit_date;
bill_detail.bill_no,item_no,amount,unit;
orders.id_no,order_text,start_date_time,stop_date_time,r_indicator('1'为失效记录、0有效),找出 bill_detail比orders多出的记录,看看有什么问题,先谢过!select b.id_no,b.name,b.visit_date,a.item_name as mc,a.amount,a.units
from bill_detail a inner join bill_master b on a.bill_no=b.bill_no
where not exists ( select 1 from bill_orders
where order_text=a.item_name
and id_no=b.id_no
and b.visit_date>=to_date('2011-10-01','yyyy-mm-dd')
and a.item_name<>'白炽灯'
and ( stop_date_time is null or
( r_indicator='0'
and start_date_time>=to_date('2011-10-01','yyyy-mm-dd')
) ) )
order by mc
bill_detail.bill_no,item_no,amount,unit;
orders.id_no,order_text,start_date_time,stop_date_time,r_indicator('1'为失效记录、0有效),找出 bill_detail比orders多出的记录,看看有什么问题,先谢过!select b.id_no,b.name,b.visit_date,a.item_name as mc,a.amount,a.units
from bill_detail a inner join bill_master b on a.bill_no=b.bill_no
where not exists ( select 1 from bill_orders
where order_text=a.item_name
and id_no=b.id_no
and b.visit_date>=to_date('2011-10-01','yyyy-mm-dd')
and a.item_name<>'白炽灯'
and ( stop_date_time is null or
( r_indicator='0'
and start_date_time>=to_date('2011-10-01','yyyy-mm-dd')
) ) )
order by mc
解决方案 »
- 用sysdba角色创建一个emp表,在删除该表时提示“删掉sys.emp错误
- 一个简单的存储过程报错,原因未知!
- insert 插入时 缺少关键字 Select 唉!....
- 新学oracle 中if inserting then什么意思?
- Oracle设置ID自动增长
- 删除表中的重复数据
- 急问!!!!!!来者有分!!!!
- 怎么访问oracle?
- 如何实现将sqlserver2008上面的数据同步到oracle11g上去?
- oracle 普通用户无法登陆
- 急:在RHEL 4下面安装Oracle 10g,报OUI-10118错误:加载安装组件时products.xml文件不存在或者读取失败
- 问一个比较弱智的问题啊
where not exists 改为
from bill_detail a, bill_master b
where a.bill_no=b.bill_no
and not exists .....
不报错啦!但运行结果比原表数据行还多
select b.id_no,b.name,b.visit_date,a.item_name as mc,a.amount,a.units
from bill_detail a, bill_master b
where a.bill_no=b.bill_no
and b.visit_date>=to_date('2011-10-01','yyyy-mm-dd')
and a.item_name<>'白炽灯'
and not exists ( select 1 from bill_orders
where order_text=a.item_name
and id_no=b.id_no
and ( stop_date_time is null or
( r_indicator='0'
and start_date_time>=to_date('2011-10-01','yyyy-mm-dd')
) ) )
order by mc
如:
select a.* from a where not exists (select 1 from b where a.id = b.id );
这样就找出了a表在b表中id不一样的记录.仅供参考
原理和sql server一模一样.例如:
select m.* from m where not exists(select 1 from n where n.id = m.id)select m.* from m where not exists(select 1 from n where n.id1 = m.id1 and n.id2 = m.id2)