1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75
| select distinct OPC.LAYER as MYLAYER,
TD.DETECTOR,
TL.TECHNOS_LAYERS,
REPLACE(RM.OWNERNAME,'.',' ') as OWNERNAME,
( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME) as nb_def,
( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
and tdp1.meta_family<>'Not_defined') as nb_defc,
( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
and tdp1.occurence > 3) as nb_def3,
( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
and tdp1.occurence > 3) as nb_defc3,
( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
and tdp1.occurence > 10) as nb_def10,
( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
and tdp1.occurence > 10 and tdp1.meta_family<>'Not_defined') as nb_defc10,
( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
and tdp1.occurence > 30) as nb_def30,
( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
and tdp1.occurence > 30 and tdp1.meta_family<>'Not_defined') as nb_defc30,
( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
and tdp1.occurence > 100) as nb_def100,
( select Count(*) from OPC OPC1, TACHYON_DEFECT_BF TD1, JOB_TACHYON JT1,TACHYON_DEFECT_PROPERTIES TDP1
where JT1.NODE = OPC1.NODE and OPC1.tech = OPC.TECH and OPC1.LAYER = opc.layer and TD1.DETECTOR= td.detector
and JT1.TACHYON_LEVEL = OPC1.ABR and TD1.JOB_ID=JT1.JOB_ID and TD1.DEFECT_NAME = TDP1.TACHYON_NAME
and tdp1.occurence > 100 and tdp1.meta_family<>'Not_defined') as nb_defc100
from OPC,
TACHYON_DEFECT_BF TD,
JOB_TACHYON JT,
( select distinct TECH,LAYER, wmsys.wm_concat(TECHNO_LAYER) TECHNOS_LAYERS
from (select distinct OPC2.TECH, OPC2.LAYER ,'{' || JT2.NODE || ' ' || JT2.TACHYON_LEVEL || '}' as TECHNO_LAYER
from JOB_TACHYON JT2, OPC OPC2
where JT2.NODE = OPC2.NODE
and OPC2.TECH='12S'
and JT2.TACHYON_LEVEL = OPC2.ABR)
group by TECH,LAYER) TL,
RET_MATRIX RM,
TECHNO_GROUPS TG,
LAYER_GROUPS LG
where JT.NODE = OPC.NODE
and OPC.TECH = TL.TECH
and JT.TACHYON_LEVEL = OPC.ABR
and TD.JOB_ID=JT.JOB_ID
and TL.LAYER = OPC.LAYER
and RM.LAYER_GROUP = LG.LAYER_GROUP
and RM.TECHNO_GROUP = TG.TECHNO_GROUP
and OPC.NODE = TG.NODE
and OPC.LAYER = LG.LAYER
order by MYLAYER,DETECTOR ASC |
Partager