db2 case when和oracle兼容问题
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
------解决方案--------------------

SQL code

create view VDA_BORROWDISPLAY(arid,arname,arcode,createdate,keyword,orgid,orgname,archivepropertype,archiveproid,secretlevel,storeterm,cabcode,facecode,gridcode,boxcode,layercode,storename,colcode,isdel,isDisplay)asselect *,       case when b1.archive_id is null then '1' else '0' end as isDisplayfrom(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) a1left 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) b1on a1.arid=b1.archive_id