MsSQL 雙層子查詢 當作主查詢的欄位條件值 產生錯誤

雙層子查詢單獨運作可以正常執行,但是將雙層子查詢的結果用來當作主查詢的欄位條件值時,就會出現錯誤。使用單一層子查詢來當作主查詢的條件值時又不會出錯。 最後只好修改雙層子查詢的語法,替換為兩個查詢條件做(and)的動作。

MSSQL 雙層子查詢語法

select MG002 from ERP.A01B.dbo.YCMSMG where MG001 in
(select MG001 from ERP.A01B.dbo.YCMSMG where MG002='0807179')
and MG002 <> '0807179'

下面是執行修改前 sql 語法的錯誤訊息,我發生的原因是因為在條件欄位前並為正確指定 Table 別名。

SqlDumpExceptionHandler: Process 60 generated fatal exception
0000005 EXCEPTION_ACCESS_VIOLATION.
SQL Server is terminating this process.

修改前的 sql 語法(發現如果子查詢的條件指定table別名就正常),這樣是可以正常運行查詢作業,但是傳回值確不是我所要的結果。

SELECT TF001,TF002,TF003,TF004 = CASE TF004 WHEN '100' THEN '事假' 
WHEN '101' THEN '病假' WHEN '102' THEN '喪假' WHEN '103' THEN '公假' 
WHEN '104' THEN '婚假' WHEN '105' THEN '產假' WHEN '106' THEN '陪產假' 
WHEN '107' THEN '生理假' WHEN '108' THEN '颱風假' WHEN '109' THEN '補休假' 
WHEN '110' THEN '特休假' WHEN '111' THEN '公傷假' WHEN '114' THEN '颱風假' END,
TF005,TF006,TF007,TF008,TF009 = CASE TF009 WHEN '1' THEN '天' WHEN '2' THEN '時' END,
TF010,TF011 = CASE TF011 WHEN 'Y' THEN '已確認' WHEN 'N' THEN '未確認' END,TF012,
TF013,TF014 = CASE TF014 WHEN '01' THEN '早班' WHEN '02' THEN '中班' 
WHEN '03' THEN '晚班' END,TF015,TF016,TF017,TF018,TF019,TF020,TF100,TF101,
TF102,TF103,TF104,TF105,TF106,TF107,TF108,TF109,TF110,TF111,TF112,TF113,TF114,
TF115,TF116,TF117 ,C.MV002 
FROM AD2SERVER.WEB.dbo.YPALTF A,ERP.A01B.dbo.YCMSMV B,
ERP.A01B.dbo.CMSMV C, ERP.A01B.dbo.YCMSMG D 
WHERE 
A.TF001=B.MV001 
AND A.TF001=C.MV001 
AND A.TF001*=D.MG002 
AND 
(B.MV100='300' 
	or (A.TF001 in (select MG002 from ERP.A01B.dbo.YCMSMG  where 
		D.MG001 in (select MG001 from ERP.A01B.dbo.YCMSMG  where MG002='0807179'))
	)
) 
AND RTRIM(A.TF101)'' 
AND A.TF001  '0807179' 
and A.TF011='N' 
ORDER BY A.TF001,A.TF002 DESC

修改後的 sql 語法,tables  join 動作在 where 之前完成。並使用微軟多資料表連結的範例來操作執行。

SELECT TF001,TF002,TF003,TF004 = CASE TF004 WHEN '100' THEN '事假' 
WHEN '101' THEN '病假' WHEN '102' THEN '喪假' WHEN '103' THEN '公假' 
WHEN '104' THEN '婚假' WHEN '105' THEN '產假' WHEN '106' THEN '陪產假' 
WHEN '107' THEN '生理假' WHEN '108' THEN '颱風假' WHEN '109' THEN '補休假' 
WHEN '110' THEN '特休假' WHEN '111' THEN '公傷假' WHEN '114' THEN '颱風假' END,
TF005,TF006,TF007,TF008,TF009 = CASE TF009 WHEN '1' THEN '天' WHEN '2' THEN '時' END,
TF010,TF011 = CASE TF011 WHEN 'Y' THEN '已確認' WHEN 'N' THEN '未確認' END,TF012,
TF013,TF014 = CASE TF014 WHEN '01' THEN '早班' WHEN '02' THEN '中班' 
WHEN '03' THEN '晚班' END,TF015,TF016,TF017,TF018,TF019,TF020,TF100,TF101,
TF102,TF103,TF104,TF105,TF106,TF107,TF108,TF109,TF110,TF111,TF112,TF113,TF114,
TF115,TF116,TF117 ,C.MV002 
FROM AD2SERVER.WEB.dbo.YPALTF A join ERP.A01B.dbo.YCMSMV B
on A.TF001=B.MV001 join ERP.A01B.dbo.CMSMV C
on A.TF001=C.MV001 left join ERP.A01B.dbo.YCMSMG D 
on A.TF001=D.MG002
WHERE 
(B.MV100='300' 
	or (A.TF001 in (select MG002 from ERP.A01B.dbo.YCMSMG  where 
		D.MG001 in (select MG001 from ERP.A01B.dbo.YCMSMG  where MG002='0807179'))
	)
) 
AND RTRIM(A.TF101)<>'' 
AND A.TF001 <> '0807179' 
and A.TF011='N' 
ORDER BY A.TF001,A.TF002 DESC

P.S: DataBase = MSSQL2000

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s