nf0EDYb5(提问者)SELECT a.packagetype,a.product_id,a.wafer_product,a.lot_id,a.lot_status_c,a.stepname,a.componentqty,
CASE WHEN a.lot_id IN (SELECT DISTINCT CHILD_LOT_ID FROM STD_MES_SPLITMERGE SM WHERE INSTR(a.lot_id,SM.LOT_ID) = 1) THEN (SELECT substr(in_step_check_date,0,15) FROM STD_MES_LOTTRACKING LT WHERE LT.LOT_ID = SUBSTR(A.LOT_ID,0,9) AND LT.STEP_ID =\'CDieBond\')
ELSE substr(a.in_step_check_date,0,15) END as in_step_check_date,
CASE WHEN (sysdate - to_date(CASE WHEN a.lot_id IN (SELECT DISTINCT CHILD_LOT_ID FROM STD_MES_SPLITMERGE SM WHERE INSTR(a.lot_id,SM.LOT_ID) = 1) THEN (SELECT substr(in_step_check_date,0,15) FROM STD_MES_LOTTRACKING LT WHERE LT.LOT_ID = SUBSTR(A.LOT_ID,0,9) AND LT.STEP_ID =\'CDieBond\')
ELSE substr(a.in_step_check_date,0,15) END,\'yyyyMMdd hh24miss\')) * 24 >120 THEN \'是\' ELSE \'否\' end AS \"120H\",
CASE WHEN (sysdate - to_date(CASE WHEN a.lot_id IN (SELECT DISTINCT CHILD_LOT_ID FROM STD_MES_SPLITMERGE SM WHERE INSTR(a.lot_id,SM.LOT_ID) = 1) THEN (SELECT substr(in_step_check_date,0,15) FROM STD_MES_LOTTRACKING LT WHERE LT.LOT_ID = SUBSTR(A.LOT_ID,0,9) AND LT.STEP_ID =\'CDieBond\')
ELSE substr(a.in_step_check_date,0,15) END,\'yyyyMMdd hh24miss\')) * 24 >148 THEN \'是\' ELSE \'否\' end AS \"148H\"
FROM etl_mes_wip_hd a
where a.stepid =\'CDieBond\'
and a.lot_status <>\'RUN\'
AND A.WAFER_PRODUCT IN(SELECT DISTINCT WAFER_PRODUCT FROM std_cat_uvwafer )
order by a.packagetype,a.product_id,a.wafer_product,a.in_step_check_date
Z4u3z1 回复 nf0EDYb5(提问者)SELECT a.packagetype,a.product_id,a.wafer_product,a.lot_id,a.lot_status_c,a.stepname,a.componentqty,
CASE WHEN a.lot_id IN (SELECT DISTINCT CHILD_LOT_ID FROM STD_MES_SPLITMERGE SM WHERE INSTR(a.lot_id,SM.LOT_ID) = 1) THEN (SELECT substr(in_step_check_date,0,15) FROM STD_MES_LOTTRACKING LT WHERE LT.LOT_ID = SUBSTR(A.LOT_ID,0,9) AND LT.STEP_ID =\'CDieBond\') ELSE substr(a.in_step_check_date,0,15) END as in_step_check_date,
CASE WHEN (sysdate - to_date(CASE WHEN a.lot_id IN (SELECT DISTINCT CHILD_LOT_ID FROM STD_MES_SPLITMERGE SM WHERE INSTR(a.lot_id,SM.LOT_ID) = 1) THEN (SELECT substr(in_step_check_date,0,15) FROM STD_MES_LOTTRACKING LT WHERE LT.LOT_ID = SUBSTR(A.LOT_ID,0,9) AND LT.STEP_ID =\'CDieBond\') ELSE substr(a.in_step_check_date,0,15) END,
\'yyyyMMdd hh24miss\')) * 24 >120 THEN \'是\' ELSE \'否\' end AS \"120H\", CASE WHEN (sysdate - to_date(CASE WHEN a.lot_id IN (SELECT DISTINCT CHILD_LOT_ID FROM STD_MES_SPLITMERGE SM WHERE INSTR(a.lot_id,SM.LOT_ID) = 1) THEN (SELECT substr(in_step_check_date,0,15) FROM STD_MES_LOTTRACKING LT WHERE LT.LOT_ID = SUBSTR(A.LOT_ID,0,9) AND LT.STEP_ID =\'CDieBond\') ELSE substr(a.in_step_check_date,0,15) END,\'yyyyMMdd hh24miss\')) * 24 >148 THEN \'是\' ELSE \'否\' end AS \"148H\" FROM etl_mes_wip_hd a where a.stepid =\'CDieBond\' and a.lot_status <>\'RUN\' AND exists (SELECT DISTINCT WAFER_PRODUCT FROM std_cat_uvwafer where a.WAFER_PRODUCT=std_cat_uvwafer.WAFER_PRODUCT ) order by a.packagetype,a.product_id,a.wafer_product,a.in_step_check_date
（oracle没用过 只有碰运气了（斜杠是论坛自己加的请删除））
WITH CTE_Dates AS (
SELECT
a.packagetype,
a.product_id,
a.wafer_product,
a.lot_id,
a.lot_status_c,
a.stepname,
a.componentqty,
COALESCE(
(SELECT substr(in_step_check_date, 0, 15)
FROM STD_MES_LOTTRACKING LT
WHERE LT.LOT_ID = SUBSTR(a.LOT_ID, 0, 9)
AND LT.STEP_ID = 'CDieBond'
),
substr(a.in_step_check_date, 0, 15)
) AS in_step_check_date_formatted
FROM
etl_mes_wip_hd a
WHERE
a.stepid = 'CDieBond'
AND a.lot_status <> 'RUN'
AND A.WAFER_PRODUCT IN (SELECT DISTINCT WAFER_PRODUCT FROM std_cat_uvwafer)
),
CTE_CheckDates AS (
SELECT
*,
CASE
WHEN (sysdate - to_date(in_step_check_date_formatted, 'yyyyMMddHH24MISS')) * 24 > 120 THEN '是' ELSE '否'
END AS "120H",
CASE
WHEN (sysdate - to_date(in_step_check_date_formatted, 'yyyyMMddHH24MISS')) * 24 > 148 THEN '是' ELSE '否'
END AS "148H"
FROM
CTE_Dates
)
SELECT
*
FROM
CTE_CheckDates
ORDER BY
packagetype,
product_id,
wafer_product,
to_date(in_step_check_date_formatted, 'yyyyMMddHH24MISS');
1min目标场景问卷
立即参与
