iT邦幫忙

0

把兩種不同資料如何合併SQL

(SELECT
A.WORK_ORDER,
A.PALLET_NO,
A.SERIAL_NUMBER,
B.PROCESS_NAME,
'10'AS STEP,
'QC CODE'AS ACTION,
case
when A.PROCESS_ID=C.PROCESS_ID or A.PROCESS_ID=F.RP_PROCESS_ID then E.DEFECT_CODE
else 'PASS'
end as "DEFECT_CODE",
A.OUT_PROCESS_TIME "OutProcessTime",
case
when A.PROCESS_ID=C.PROCESS_ID or A.PROCESS_ID=F.RP_PROCESS_ID then NVL(F.REMARK,SAJET.sj_get_repair_remark(F.RECID))
else null
end as "備註(Remark/Defect Item)",
case
when A.PROCESS_ID=F.RP_PROCESS_ID then F.REPAIR_TIME
else null
end as "(Repair Time)",
Case
when A.PROCESS_ID=C.PROCESS_ID or A.PROCESS_ID=F.RP_PROCESS_ID then G.REASON_CODE
else null
end as "原因代碼(Reason Code)",
case
when A.PROCESS_ID=C.PROCESS_ID or A.PROCESS_ID=F.RP_PROCESS_ID then G.REASON_DESC
else null
end as "原因描述(Reason Desc)",
case
when A.PROCESS_ID=C.PROCESS_ID or A.PROCESS_ID=F.RP_PROCESS_ID then F.LOCATION
else null
end as "位置(Location)"
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 A.SERIAL_NUMBER ='IXJ146088')aa 無資料 ,因為沒有經過包裝跟維修倉

(SELECT
A.WORK_ORDER,
A.PALLET_NO,
A.SERIAL_NUMBER,
B.PROCESS_NAME,
'10'AS STEP,
C.BARCODE AS ACTION
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 A.PROCESS_ID ='100056'
AND A.SERIAL_NUMBER ='IXJ146088')bb有資料

請問一下,如何aa跟bb資料兩個合併顯示下列欄位https://ithelp.ithome.com.tw/upload/images/20190314/20116136wNTej8jOg4.png

JOIN

2 個回答

2
純真的人
iT邦高手 1 級 ‧ 2019-03-14 09:56:40
最佳解答

用union all後面欄位不足的用null補足

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.PROCESS_ID=C.PROCESS_ID or A.PROCESS_ID=F.RP_PROCESS_ID 
		then E.DEFECT_CODE
		else 'PASS'
		end as "DEFECT_CODE"
	,A.OUT_PROCESS_TIME "OutProcessTime"
	,
		case
		when A.PROCESS_ID=C.PROCESS_ID or A.PROCESS_ID=F.RP_PROCESS_ID 
		then NVL(F.REMARK,SAJET.sj_get_repair_remark(F.RECID))
		else null
		end as "備註(Remark/Defect Item)"
	,
		case
		when A.PROCESS_ID=F.RP_PROCESS_ID 
		then F.REPAIR_TIME
		else null
		end as "(Repair Time)"
	,
		Case
		when A.PROCESS_ID=C.PROCESS_ID or A.PROCESS_ID=F.RP_PROCESS_ID 
		then G.REASON_CODE
		else null
		end as "原因代碼(Reason Code)"
	,
		case
		when A.PROCESS_ID=C.PROCESS_ID or A.PROCESS_ID=F.RP_PROCESS_ID 
		then G.REASON_DESC
		else null
		end as "原因描述(Reason Desc)"
	,
		case
		when A.PROCESS_ID=C.PROCESS_ID or A.PROCESS_ID=F.RP_PROCESS_ID 
		then F.LOCATION
		else null
		end as "位置(Location)"
	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 A.SERIAL_NUMBER ='IXJ146088'
) union all (
	SELECT A.WORK_ORDER
	,A.PALLET_NO
	,A.SERIAL_NUMBER
	,B.PROCESS_NAME
	,'10'AS STEP
	,C.BARCODE AS ACTION
	,null
	,null
	,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 A.PROCESS_ID ='100056'
)) as k
看更多先前的回應...收起先前的回應...

你括號太多了吧,有些真的有需要那樣包起來嗎?

一級屠豬士
寫習慣了~區分顯示區塊呢~

你這樣有比較好區分嗎?
蠻多多餘的區塊, union all 不需要包,
你最後又全包,再 select *
,不是不行啦. 但是也不會比較清楚.

哈~已經只能這樣確認位置了

0
maxchen49
iT邦新手 5 級 ‧ 2019-03-14 17:17:55

union all
缺的欄位用 '' 或 null 補齊

不建議空字串...會引發欄位類型錯誤~
因為他是數字..你卻給他空字串..
或者他是日期..你卻給他空字串..

我要發表回答

立即登入回答