Oracle statement code
Create or replace view qua. view_lose_check_request_cut
Select M. Wo, M. Lot, M. mat_id, M. routing_id, M. tech_id, M. start_time, N. proc_id from
(
Select. wo wo,. lot, B. id mat_id, C. id routing_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 = A. mat_id and C. CTRL = A. routing_id
Where wo_grp_id in (2, 3, 4) and B. Cd = A. mat_id and C. CTRL = 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 = 1 and P. ID = TD. proc_id
) N
Where M. mat_id = n. mat_id and M. routing_id = n. routing_id and M. tech_id = 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
)
After converting to 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 = B. CD inner join
Pub. Routing as C on A. routing_id = 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 = P. ID
Where (P. is_check = 1) as N on M. mat_id = n. mat_id and M. routing_id = n. routing_id and M. tech_id = 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 = S. WO) and (lot_id = S. Lot) as HH
Where (M. Wo = Wo) and (M. Lot = lot ))