ORACLE语句
Code
create or replace view qua.view_lose_check_request_cut as
select m.wo,m.lot ,m.mat_id,m.routing_id,m.tech_id,m.start_time,n.proc_id from
(
select a.wo wo ,a.lot lot ,b.id mat_id,c.id routing_id,a.tech_id tech_id,start_time from dps.wo_trace a,pub.mat b,pub.routing c
--where wo_grp_id in (2,3,4) and b.ctrl&＃61;a.mat_id and c.ctrl&＃61;a.routing_id
where wo_grp_id in (2,3,4) and b.cd &＃61;a.mat_id and c.ctrl&＃61;a.routing_id
) m
,
(
--¼ìÑ鹤Ðò
select distinct td.mat_id,td.routing_id,td.tech_id,td.proc_id from spc.standred_info2 td,pub.proc p
where p.is_check&＃61;1 and p.id&＃61;td.proc_id
) n
where m.mat_id&＃61;n.mat_id and m.routing_id&＃61;n.routing_id and m.tech_id&＃61;n.tech_id and (m.wo,m.lot)
in
(
select hh.wo,hh.lot from
(
select wo,lot from dps.wo_trace
minus
select wo_id,lot_id from qua.check_request
) hh
)
转成MSSQL后
Code
SELECT m.wo, m.lot, m.mat_id, m.routing_id, m.tech_id, m.START_TIME, n.PROC_ID
FROM (SELECT a.WO AS wo, a.LOT AS lot, b.ID AS mat_id, c.ID AS routing_id, a.TECH_ID AS tech_id, a.START_TIME
FROM dps.WO_TRACE AS a INNER JOIN
pub.MAT AS b ON a.MAT_ID &＃61; b.CD INNER JOIN
pub.ROUTING AS c ON a.ROUTING_ID &＃61; c.CTRL
WHERE (a.WO_GRP_ID IN (2, 3, 4))) AS m INNER JOIN
(SELECT DISTINCT td.MAT_ID, td.ROUTING_ID, td.TECH_ID, td.PROC_ID
FROM spc.STANDRED_INFO2 AS td INNER JOIN
pub.[PROC] AS p ON td.PROC_ID &＃61; p.ID
WHERE (p.IS_CHECK &＃61; 1)) AS n ON m.mat_id &＃61; n.MAT_ID AND m.routing_id &＃61; n.ROUTING_ID AND m.tech_id &＃61; n.TECH_ID
WHERE EXISTS
(SELECT WO, LOT
FROM (SELECT WO, LOT
FROM dps.WO_TRACE AS s
WHERE (NOT EXISTS
(SELECT REQUEST_ID, WO_ID, LOT_ID, MAT_ID, PROC_ID, REQUEST_TYPE_ID, REQUEST_STATE, START_TIME,
END_TIME, SAMPLE_PLACE_CODE, SAMPLE_PLACE_NAME, SHIFT_ID, MAT_TYPE, ROUTING_ID, TECH_ID,
WO_ID2
FROM qua.CHECK_REQUEST AS b
WHERE (WO_ID &＃61; s.WO) AND (LOT_ID &＃61; s.LOT)))) AS hh
WHERE (m.wo &＃61; WO) AND (m.lot &＃61; LOT))
Codecreate or replace view qua.view_lose_check_request_cut as
select m.wo,m.lot ,m.mat_id,m.routing_id,m.tech_id,m.start_time,n.proc_id from
(
select a.wo wo ,a.lot lot ,b.id mat_id,c.id routing_id,a.tech_id tech_id,start_time from dps.wo_trace a,pub.mat b,pub.routing c
--where wo_grp_id in (2,3,4) and b.ctrl&＃61;a.mat_id and c.ctrl&＃61;a.routing_id
where wo_grp_id in (2,3,4) and b.cd &＃61;a.mat_id and c.ctrl&＃61;a.routing_id
) m
,
(
--¼ìÑ鹤Ðò
select distinct td.mat_id,td.routing_id,td.tech_id,td.proc_id from spc.standred_info2 td,pub.proc p
where p.is_check&＃61;1 and p.id&＃61;td.proc_id
) n
where m.mat_id&＃61;n.mat_id and m.routing_id&＃61;n.routing_id and m.tech_id&＃61;n.tech_id and (m.wo,m.lot)
in
(
select hh.wo,hh.lot from
(
select wo,lot from dps.wo_trace
minus
select wo_id,lot_id from qua.check_request
) hh
)
转成MSSQL后
CodeSELECT m.wo, m.lot, m.mat_id, m.routing_id, m.tech_id, m.START_TIME, n.PROC_ID
FROM (SELECT a.WO AS wo, a.LOT AS lot, b.ID AS mat_id, c.ID AS routing_id, a.TECH_ID AS tech_id, a.START_TIME
FROM dps.WO_TRACE AS a INNER JOIN
pub.MAT AS b ON a.MAT_ID &＃61; b.CD INNER JOIN
pub.ROUTING AS c ON a.ROUTING_ID &＃61; c.CTRL
WHERE (a.WO_GRP_ID IN (2, 3, 4))) AS m INNER JOIN
(SELECT DISTINCT td.MAT_ID, td.ROUTING_ID, td.TECH_ID, td.PROC_ID
FROM spc.STANDRED_INFO2 AS td INNER JOIN
pub.[PROC] AS p ON td.PROC_ID &＃61; p.ID
WHERE (p.IS_CHECK &＃61; 1)) AS n ON m.mat_id &＃61; n.MAT_ID AND m.routing_id &＃61; n.ROUTING_ID AND m.tech_id &＃61; n.TECH_ID
WHERE EXISTS
(SELECT WO, LOT
FROM (SELECT WO, LOT
FROM dps.WO_TRACE AS s
WHERE (NOT EXISTS
(SELECT REQUEST_ID, WO_ID, LOT_ID, MAT_ID, PROC_ID, REQUEST_TYPE_ID, REQUEST_STATE, START_TIME,
END_TIME, SAMPLE_PLACE_CODE, SAMPLE_PLACE_NAME, SHIFT_ID, MAT_TYPE, ROUTING_ID, TECH_ID,
WO_ID2
FROM qua.CHECK_REQUEST AS b
WHERE (WO_ID &＃61; s.WO) AND (LOT_ID &＃61; s.LOT)))) AS hh
WHERE (m.wo &＃61; WO) AND (m.lot &＃61; LOT))
京公网安备 11010802041100号