create view VDA_BORROWDISPLAY
(arid,arname,arcode,
createdate,keyword,orgid,
orgname,archivepropertype,archiveproid,
secretlevel,storeterm,
cabcode,facecode,gridcode,
boxcode,layercode,storename,
colcode,isdel,isDisplay)
as
select c.ID arid,c.name arname,c.code arcode,
c.create_date createdate,c.keyword keyword ,organ.organid orgid,
organ.organname orgname, archivepro.name archivepropertype,c.archive_pro_id archiveproid,
enum.name secretlevel,enum1.name storeterm,
cab.code cabcode,face.llabel facecode,grid.code gridcode,
box.code boxcode,layer.code layercode,storehouse.name storename,
col.code colcode,c.isdel isdel,
case
when c.id in (select b.archive_id
from tda_borrow_list a, tda_borrow_detail b
where a.id = b.borrow_list_id
and a.return_date is null) then
'0'
else
'1'
end as isDisplay
from tda_archive c
left JOIN TORG_ORGAN organ ON c.org_id=organ.organid
left JOIN TDM_ENUMITEM enum on c.secret_level=enum.enumid
left JOIN TDM_ENUMITEM enum1 on c.storeterm_id=enum1.enumid
left JOIN TDA_PLACE place on c.place_id=place.id
left JOIN Tda_Col col on col.id=place.col_id
inner JOIN tda_archive_pro archivepro on c.archive_pro_id=archivepro.id
inner JOIN Tda_Cabinet cab on cab.id=place.cabinet_id
inner JOIN Tda_Face face on face.id=place.face_id
inner JOIN Tda_Grid grid on grid.id=place.grid_id
inner JOIN Tda_Layer layer on layer.id=place.layer_id
inner JOIN Tda_Box box on box.id=place.box_id
inner JOIN Tda_Store_House storehouse on storehouse.id=place.store_house_id
以上是视图 该视图在oracle上可以 db2上报错
具体问题如下：
16:43:52 [CREATE - 0 row(s), 0.000 secs] [Error Code: -115, SQL State: 42601] [SQL0115] Comparison operator IN not valid.
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
急啊！求朋友帮忙解决下！
------解决方案--------------------------------------------------------
select *,case when b1.archive_id is null then '1' else '0' end as isDisplay from (
select c.ID arid,c.name arname,c.code arcode,
c.create_date createdate,c.keyword keyword ,organ.organid orgid,
organ.organname orgname, archivepro.name archivepropertype,c.archive_pro_id archiveproid,
enum.name secretlevel,enum1.name storeterm,
cab.code cabcode,face.llabel facecode,grid.code gridcode,
box.code boxcode,layer.code layercode,storehouse.name storename,
col.code colcode,c.isdel isdel
from tda_archive c
left JOIN TORG_ORGAN organ ON c.org_id=organ.organid
left JOIN TDM_ENUMITEM enum on c.secret_level=enum.enumid
left JOIN TDM_ENUMITEM enum1 on c.storeterm_id=enum1.enumid
left JOIN TDA_PLACE place on c.place_id=place.id
left JOIN Tda_Col col on col.id=place.col_id
inner JOIN tda_archive_pro archivepro on c.archive_pro_id=archivepro.id
inner JOIN Tda_Cabinet cab on cab.id=place.cabinet_id
inner JOIN Tda_Face face on face.id=place.face_id
inner JOIN Tda_Grid grid on grid.id=place.grid_id
inner JOIN Tda_Layer layer on layer.id=place.layer_id
inner JOIN Tda_Box box on box.id=place.box_id
inner JOIN Tda_Store_House storehouse on storehouse.id=place.store_house_id) a1
left join
(select b.archive_id
from tda_borrow_list a, tda_borrow_detail b
where a.id = b.borrow_list_id
and a.return_date is null) b1
on a1.arid=b1.archive_id
------解决方案--------------------------------------------------------