SELECT *
FROM (
(SELECT A.WORK_ORDER
,A.PALLET_NO
,A.SERIAL_NUMBER
,B.PROCESS_NAME
,'10'AS STEP
,'QC CODE'AS ACTION
,
case
when A.OUT_PROCESS_TIME=C.REC_TIME or A.PROCESS_ID=F.RP_PROCESS_ID
then E.DEFECT_CODE
else 'PASS'
end as "DEFECT_CODE"
,
case
when A.OUT_PROCESS_TIME=C.REC_TIME
then E.DEFECT_DESC
else null
end as "不良描述1"
,
A.OUT_PROCESS_TIME "OutProcessTime"
,
case
when A.OUT_PROCESS_TIME=C.REC_TIME
then F.REPAIR_TIME
else null
end as "(Repair Time)"
,
Case
when A.OUT_PROCESS_TIME=C.REC_TIME or A.PROCESS_ID=F.RP_PROCESS_ID
then G.REASON_CODE
else null
end as "原因代碼(Reason Code)"
,
case
when A.OUT_PROCESS_TIME=C.REC_TIME or A.PROCESS_ID=F.RP_PROCESS_ID
then G.REASON_DESC
else null
end as "原因描述(Reason Desc)"
,
case
when A.OUT_PROCESS_TIME=C.REC_TIME or A.PROCESS_ID=F.RP_PROCESS_ID
then F.LOCATION
else null
end as "位置(Location)"
,
case
when A.OUT_PROCESS_TIME=C.REC_TIME or A.PROCESS_ID=F.RP_PROCESS_ID
then H.DUTY_DESC
else null
end as "責任描述(DutyDesc)"
FROM SAJET.G_SN_TRAVEL A
,SAJET.SYS_PROCESS B
,SAJET.G_SN_DEFECT C
, SAJET.SYS_DEFECT E
, SAJET.G_SN_REPAIR F
, SAJET.SYS_REASON G
, SAJET.SYS_DUTY H
WHERE A.PROCESS_ID = B.PROCESS_ID
AND A.SERIAL_NUMBER=C.SERIAL_NUMBER
AND E.DEFECT_ID=C.DEFECT_ID
AND F.RECID=C.RECID
AND F.REASON_ID=G.REASON_ID
AND F.DUTY_ID=H.DUTY_ID
AND B.PROCESS_ID NOT IN '100056'
AND B.PROCESS_NAME not in 'MAC MAP'
AND B.PROCESS_NAME NOT LIKE ('%REPAIR%')
AND A.SERIAL_NUMBER ='IXJ147241' )
UNION ALL
(SELECT A.WORK_ORDER
,A.PALLET_NO
,A.SERIAL_NUMBER
,B.PROCESS_NAME
,'10'AS STEP
,'Install component - S/N' AS ACTION
,C.BARCODE AS DEFECT_CODE
,null
,A.OUT_PROCESS_TIME
,null
,null
,null
,null
,null
FROM SAJET.G_SN_TRAVEL A,SAJET.SYS_PROCESS B , SAJET.G_BARCODE C
WHERE A.PROCESS_ID = B.PROCESS_ID
AND A.SERIAL_NUMBER = C.SERIAL_NUMBER
AND B.PROCESS_NAME ='MAC MAP'
AND A.SERIAL_NUMBER ='IXJ147241'))

!!![https://ithelp.ithome.com.tw/upload/images/20190320/20116136Bl3Rdbhnhw.png](https://ithelp.ithome.com.tw/upload/images/20190320/20116136Bl3Rdbhnhw.png)