LNNVL為應用於WHERE子句中的篩選邏輯函數，如資料符合篩選條件（true）即傳回false；如資料為UNKNOWN（NULL）或不符合（false）則傳回true，如說明應改用負向條件，且不可使用複合運算式，如AND, OR, or BETWEEN。語法如下:
LNNVL(<condition>)
|
本範例將查詢2016-02-29後仍在職人員，即離職日期需大於此查詢日期或為NULL值，如LNNVL函數定義將離職日期改為小於等於2016-02-29。
SELECT A.*
, CASE WHEN Leave_Date IS NULL THEN 1 ELSE 0 END JUDGE1
, CASE WHEN Leave_Date <= '20160229' THEN 0 ELSE 1 END JUDGE2
, CASE WHEN Leave_Date IS NULL THEN 1 ELSE 0 END
+ CASE WHEN Leave_Date <= '20160229' THEN 0 ELSE 1 END JUDGE
FROM
(
SELECT 'Sam' Emp_Name, '20160229' Leave_Date
FROM DUAL
UNION ALL
SELECT 'Tom' Emp_Name, NULL Leave_Date
FROM DUAL
UNION ALL
SELECT 'Kevin' Emp_Name, NULL Leave_Date
FROM DUAL
UNION ALL
SELECT 'Simon' Emp_Name, '20170126' Leave_Date
FROM DUAL
UNION ALL
SELECT 'Alex' Emp_Name, '20150630' Leave_Date
FROM DUAL
) A
WHERE 1=1
--AND (Leave_Date IS NULL OR
Leave_Date > '20160229') --方法1
--AND
LNNVL(Leave_Date<='20160229') --方法2
--AND COALESCE(Leave_Date,
'99991231') >'20160229' --方法3
為說明函數運行概念，於查詢中增加二個運算式欄位（JUDGE1、JUDGE2）及前兩者OR運算欄位（JUDGE）並整理如下表，並列出三種方法，請自行測試。
:
#
|
判斷式
|
說明
|
1
|
Leave_Date
IS NULL
|
離職日期為未知
|
2
|
Leave_Date <=
'20160229'
|
離職日期小於等於20160229
|
雖官方文件說明此函數為WHERE子句中，但仍嘗試應用於SELECT子句中，SQL如下。由於此函數傳回值為boolean值(true/false)，因此無法直接顯示及應用而轉換為NULL，但仍可搭配CASE WHEN子句使用。
SELECT CASE WHEN LNNVL(1=0) THEN 0 ELSE 1 END "1=0"
, CASE WHEN LNNVL(1=1) THEN 0 ELSE 1 END "1=1"
, LNNVL(1=0) "1=0"
, LNNVL(1=1) "1=0"
FROM DUAL
註: 本範例為常見違反設計原則的案例
1. 以日期資料以字串方式儲存
日期合法性已無法由資料庫進行檢核，且無法直接使用日期運算函數。
2. 未使用預設值
雖現職人員並無離職日期，建議可設定預設值為9999-12-31，應避免為NULL，否則查詢/應用時常需進行額外轉換。
沒有留言:
張貼留言