两个select单独能运行,为什么加上union就报ora-01722 invalid number
selectlot.lot_id,L4.l4_mat_desc,lot.lot_status,fea.inspect_feature_id,fea.inspect_fea...
select lot.lot_id,
L4.l4_mat_desc,
lot.lot_status,
fea.inspect_feature_id,
fea.inspect_feature_name,
dat.inspect_item_value,
op.created_dt,
lot.wip_status,decode(fea.value_type,1,case when dat.target_value=dat.inspect_item_value then 1 else 0 end,2,case when dat.inspect_item_value > dat.upper_value then 0 when dat.inspect_item_value<dat.lower_value then 0 else 1 end)qc
from hp_pe_lot_op_inspect_data dat,
hp_pe_lot_operation op,
hp_qm_op_inspect_plan_item item,
hp_pe_lot lot,
hp_qm_inspect_feature fea,
hp_md_material mat,
hp_md_l4_material L4
where op.sid = dat.lot_operation_sid
and fea.sid = dat.inspect_feature_sid
and item.inspect_feature_sid=fea.sid
and mat.sid = lot.material_sid
and L4.l4_mat_id = mat.l4_mat_id
and lot.sid = op.lot_sid
union
--2样片检验
select lot.lot_id,
L4.l4_mat_desc,
lot.lot_status,
fea.inspect_feature_id,
fea.inspect_feature_name,
item.actual_value,
op.created_dt,
lot.wip_status,sam.qa_flag qc
from hp_pe_lot lot,
hp_qm_sample sam,
hp_qm_sample_item item,
hp_qm_inspect_feature fea,
hp_md_material mat,
hp_md_l4_material L4,
hp_pe_lot_operation op,
hp_qm_sample_set se
where --lot.sid = sam.lot_sid
sam.sid = item.sample_sid
and fea.sid = item.inspect_feature_sid
and mat.sid = lot.material_sid
and L4.l4_mat_id = mat.l4_mat_id
and lot.sid = op.lot_sid
and se.sid = lot.sample_set_sid
and se.sid = sam.sample_set_sid
and sam.valid_flag=1 展开
L4.l4_mat_desc,
lot.lot_status,
fea.inspect_feature_id,
fea.inspect_feature_name,
dat.inspect_item_value,
op.created_dt,
lot.wip_status,decode(fea.value_type,1,case when dat.target_value=dat.inspect_item_value then 1 else 0 end,2,case when dat.inspect_item_value > dat.upper_value then 0 when dat.inspect_item_value<dat.lower_value then 0 else 1 end)qc
from hp_pe_lot_op_inspect_data dat,
hp_pe_lot_operation op,
hp_qm_op_inspect_plan_item item,
hp_pe_lot lot,
hp_qm_inspect_feature fea,
hp_md_material mat,
hp_md_l4_material L4
where op.sid = dat.lot_operation_sid
and fea.sid = dat.inspect_feature_sid
and item.inspect_feature_sid=fea.sid
and mat.sid = lot.material_sid
and L4.l4_mat_id = mat.l4_mat_id
and lot.sid = op.lot_sid
union
--2样片检验
select lot.lot_id,
L4.l4_mat_desc,
lot.lot_status,
fea.inspect_feature_id,
fea.inspect_feature_name,
item.actual_value,
op.created_dt,
lot.wip_status,sam.qa_flag qc
from hp_pe_lot lot,
hp_qm_sample sam,
hp_qm_sample_item item,
hp_qm_inspect_feature fea,
hp_md_material mat,
hp_md_l4_material L4,
hp_pe_lot_operation op,
hp_qm_sample_set se
where --lot.sid = sam.lot_sid
sam.sid = item.sample_sid
and fea.sid = item.inspect_feature_sid
and mat.sid = lot.material_sid
and L4.l4_mat_id = mat.l4_mat_id
and lot.sid = op.lot_sid
and se.sid = lot.sample_set_sid
and se.sid = sam.sample_set_sid
and sam.valid_flag=1 展开
展开全部
首先when dat.inspect_item_value > dat.upper_value then 0 when dat.inspect_item_value<dat.lower_value then 0 else 1 end
可以换成
when dat.inspect_item_value <> dat.upper_value then 0
ora-01722 说明有可能是你的参数类型不对,有的id可能是number,有的可能是varchar,如果用这俩个字段比较要转换成同一类型才能比较,你的错误在哪一行,可以从那里入手,plsql应该有提示的,就是一个小小的红线。
and sam.valid_flag=1这个条件里看看sam.valid_flag类型是否为数字型
可以换成
when dat.inspect_item_value <> dat.upper_value then 0
ora-01722 说明有可能是你的参数类型不对,有的id可能是number,有的可能是varchar,如果用这俩个字段比较要转换成同一类型才能比较,你的错误在哪一行,可以从那里入手,plsql应该有提示的,就是一个小小的红线。
and sam.valid_flag=1这个条件里看看sam.valid_flag类型是否为数字型
追问
非常感谢,这位朋友的回答,我又运行了一下看了一下没有指示错误具体位置的小红线。两个语句单独执行是没问题的,就是用union连接起来后就报此错误,还有如果把decode里面是case部分直接换成数字1或者0也可以执行,把decode部分直接去掉也可以执行,估计就是decode处有问题,但我真查不出来,到底怎么回事,到底谁的类型跟谁的类型不匹配啊,望高手指点!
追答
把你的建表语句发过来,我看下,号码就是我的名字,加好友时请注明从百度贴吧看到的
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询