快累死了!
SELECT a.cnt_pass, DECODE (b.cnt_fail, NULL, 0, b.cnt_fail) cnt_fail,
e.lob_no, TO_CHAR (SYSDATE, 'YYYY-MM-DD') today, a.line,
(a.cnt_pass + DECODE (b.cnt_fail, NULL, 0, b.cnt_fail)) total_cnt,
DECODE ( ROUND ((a.cnt_pass / (b.cnt_fail + a.cnt_pass)) * 100, 1)
|| '%',
'%', '100%',
ROUND ((a.cnt_pass / (b.cnt_fail + a.cnt_pass)) * 100, 1)
|| '%'
) pass_yield,
DECODE (c.cnt_repair, NULL, 0, cnt_repair) cnt_repair,
DECODE (d.cnt_ng, NULL, 0, d.cnt_ng) cnt_ng,
DECODE (b.cnt_fail, NULL, 0, b.cnt_fail)
- ( DECODE (c.cnt_repair, NULL, 0, cnt_repair)
+ DECODE (d.cnt_ng, NULL, 0, d.cnt_ng)
) wait_repair,
DECODE (ROUND ( ( ( DECODE (c.cnt_repair, NULL, 0, cnt_repair)
+ DECODE (d.cnt_ng, NULL, 0, d.cnt_ng)
)
/ b.cnt_fail
)
* 100,
1
),
NULL, NULL,
ROUND ( ( ( DECODE (c.cnt_repair, NULL, 0, cnt_repair)
+ DECODE (d.cnt_ng, NULL, 0, d.cnt_ng)
)
/ b.cnt_fail
)
* 100,
1
)
|| '%'
) repair_yield
FROM (SELECT SUM (a.mb_cnt) cnt_pass, a.line
FROM mb_cnt_by_time a
WHERE a.work_order='06D000001E'
AND a.dept || a.station = 'D50'
GROUP BY a.line
ORDER BY a.line) a,
(SELECT line, COUNT (barcode_no) cnt_fail
FROM (SELECT a.work_order, a.line, SUBSTR (f.item_no, 5) AS gr,
a.barcode_no
FROM ng_barcode a,
ng_component_info b,
ng_error_info c,
work_station d,
work_order_info_detail f,
data_station i
WHERE station = 10
AND d.work_station_id = i.work_station_id
AND i.data_status = 'F'
AND a.work_order='06D000001E'
AND a.barcode_id = b.barcode_id
AND b.component_id = c.component_id(+)
AND a.station = d.work_station_id
AND a.work_order = f.work_order
AND f.TYPE = 'D'
UNION ALL
SELECT work_order, line, model AS gr, a.barcode_no
FROM repair_info a
WHERE dept = 'D'
AND SUBSTR (test_no, 1, 1) = 'T'
AND WORK_ORDER='06D000001E')
GROUP BY line) b,
(SELECT COUNT (DISTINCT a.barcode_no) cnt_repair, a.line
FROM repair_info a,
(SELECT code, descript rma_des, responsibility_dept,
issue_belong
FROM repair_code_des
WHERE codetype <> 'E') b,
(SELECT code ERROR_CODE, descript error_des
FROM repair_code_des
WHERE codetype = 'E') c
WHERE a.code IS NOT NULL
AND SUBSTR (a.status, 2, 1) || SUBSTR (a.status, 5, 2) = 'D30'
AND dept = 'D'
AND WORK_ORDER='06D000001E'
AND complete_date IS NOT NULL
AND a.code = b.code(+)
AND bad_code = ERROR_CODE(+)
GROUP BY a.line
ORDER BY a.line) c,
(SELECT COUNT (DISTINCT a.barcode_no) cnt_ng, a.line
FROM ng_barcode a,
ng_component_info b,
ng_error_info c,
work_station d,
repair_code_des g,
machine_test_info h,
data_station i
WHERE repair_date IS NOT NULL
AND station = 10
AND SUBSTR (i.data_station_no, 1, 1)
|| SUBSTR (i.data_station_no, 2, 2) = 'D30'
AND g.code = b.repair_code
AND d.work_station_id = h.work_station_id
AND a.barcode_no = h.barcode_no
AND d.work_station_id = i.work_station_id
AND i.data_status = 'F'
AND (a.WORK_ORDER='06D000001E'
)
AND a.barcode_id = b.barcode_id
AND b.component_id = c.component_id(+)
AND a.station = d.work_station_id
GROUP BY a.line
ORDER BY a.line) d,
mes_bn1.line_info e
WHERE a.line = b.line(+)
AND a.line = c.line(+)
AND a.line = d.line(+)
AND e.line_desc = a.line
AND e.unit_no = 'S'
ORDER BY e.lob_no, a.line
SELECT a.cnt_pass, DECODE (b.cnt_fail, NULL, 0, b.cnt_fail) cnt_fail,
e.lob_no, TO_CHAR (SYSDATE, 'YYYY-MM-DD') today, a.line,
(a.cnt_pass + DECODE (b.cnt_fail, NULL, 0, b.cnt_fail)) total_cnt,
DECODE ( ROUND ((a.cnt_pass / (b.cnt_fail + a.cnt_pass)) * 100, 1)
|| '%',
'%', '100%',
ROUND ((a.cnt_pass / (b.cnt_fail + a.cnt_pass)) * 100, 1)
|| '%'
) pass_yield,
DECODE (c.cnt_repair, NULL, 0, cnt_repair) cnt_repair,
DECODE (d.cnt_ng, NULL, 0, d.cnt_ng) cnt_ng,
DECODE (b.cnt_fail, NULL, 0, b.cnt_fail)
- ( DECODE (c.cnt_repair, NULL, 0, cnt_repair)
+ DECODE (d.cnt_ng, NULL, 0, d.cnt_ng)
) wait_repair,
DECODE (ROUND ( ( ( DECODE (c.cnt_repair, NULL, 0, cnt_repair)
+ DECODE (d.cnt_ng, NULL, 0, d.cnt_ng)
)
/ b.cnt_fail
)
* 100,
1
),
NULL, NULL,
ROUND ( ( ( DECODE (c.cnt_repair, NULL, 0, cnt_repair)
+ DECODE (d.cnt_ng, NULL, 0, d.cnt_ng)
)
/ b.cnt_fail
)
* 100,
1
)
|| '%'
) repair_yield
FROM (SELECT SUM (a.mb_cnt) cnt_pass, a.line
FROM mb_cnt_by_time a
WHERE a.work_order='06D000001E'
AND a.dept || a.station = 'D50'
GROUP BY a.line
ORDER BY a.line) a,
(SELECT line, COUNT (barcode_no) cnt_fail
FROM (SELECT a.work_order, a.line, SUBSTR (f.item_no, 5) AS gr,
a.barcode_no
FROM ng_barcode a,
ng_component_info b,
ng_error_info c,
work_station d,
work_order_info_detail f,
data_station i
WHERE station = 10
AND d.work_station_id = i.work_station_id
AND i.data_status = 'F'
AND a.work_order='06D000001E'
AND a.barcode_id = b.barcode_id
AND b.component_id = c.component_id(+)
AND a.station = d.work_station_id
AND a.work_order = f.work_order
AND f.TYPE = 'D'
UNION ALL
SELECT work_order, line, model AS gr, a.barcode_no
FROM repair_info a
WHERE dept = 'D'
AND SUBSTR (test_no, 1, 1) = 'T'
AND WORK_ORDER='06D000001E')
GROUP BY line) b,
(SELECT COUNT (DISTINCT a.barcode_no) cnt_repair, a.line
FROM repair_info a,
(SELECT code, descript rma_des, responsibility_dept,
issue_belong
FROM repair_code_des
WHERE codetype <> 'E') b,
(SELECT code ERROR_CODE, descript error_des
FROM repair_code_des
WHERE codetype = 'E') c
WHERE a.code IS NOT NULL
AND SUBSTR (a.status, 2, 1) || SUBSTR (a.status, 5, 2) = 'D30'
AND dept = 'D'
AND WORK_ORDER='06D000001E'
AND complete_date IS NOT NULL
AND a.code = b.code(+)
AND bad_code = ERROR_CODE(+)
GROUP BY a.line
ORDER BY a.line) c,
(SELECT COUNT (DISTINCT a.barcode_no) cnt_ng, a.line
FROM ng_barcode a,
ng_component_info b,
ng_error_info c,
work_station d,
repair_code_des g,
machine_test_info h,
data_station i
WHERE repair_date IS NOT NULL
AND station = 10
AND SUBSTR (i.data_station_no, 1, 1)
|| SUBSTR (i.data_station_no, 2, 2) = 'D30'
AND g.code = b.repair_code
AND d.work_station_id = h.work_station_id
AND a.barcode_no = h.barcode_no
AND d.work_station_id = i.work_station_id
AND i.data_status = 'F'
AND (a.WORK_ORDER='06D000001E'
)
AND a.barcode_id = b.barcode_id
AND b.component_id = c.component_id(+)
AND a.station = d.work_station_id
GROUP BY a.line
ORDER BY a.line) d,
mes_bn1.line_info e
WHERE a.line = b.line(+)
AND a.line = c.line(+)
AND a.line = d.line(+)
AND e.line_desc = a.line
AND e.unit_no = 'S'
ORDER BY e.lob_no, a.line
解决方案 »
- to_date,select 显示正常,update报错01840,输入值对于日期格式不够长
- 多表查询问题
- 11g rac中 root.sh的执行报错
- 请教哪本讲oracle9i的DBA的书是最好的?
- 一个ASP程序,查询Oracle数据库时调用to_char函数遇到的问题,请一定进来看看,拜托了
- 关于导入导出的问题.
- 请问数据库安全审计的数据字典视图有哪些呢?急急急。。。 请各位帮帮我 万分感谢!
- OEM里的目录树里没有数据库这一项,这是怎么回事啊??
- 再问:我执行一个存储过程,提示:ORA-01006: 赋值变量不存在.详细在内,请指教,谢谢!(Hothehe)
- 重启电脑之后,plsql登录不了本地数据库
- 求树形层次结构组织数据_ 高手帮忙解决下阿。谢谢了
- 求助SQL语句的写法,谢谢!
不过,能不能分出几条简单的sql啊,这样的话,以后维护或者什么的都很方便~
难道非要用一条sql语句来实现嘛?
这句可以简化为
nvl(b.cnt_fail, 0)
没有实际环境,实在是很不喜欢看啊!