5,889
社区成员
发帖
与我相关
我的任务
分享
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 *,
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