我有下表，我想用DATE_FORM變數控制選擇。
為什么我的 T-SQL 代碼不起作用？有人有想法嗎？
表格1
-- @DATE_FORM = 1 select a particular INFO-DATE INTERVALL
-- @DATE_FORM = 2 select a particular ORDER-DATE INTERVALL
-- @DATE_FORM = 3 select a particular SHIPPING INTERVALL
DECLARE @DATE_FORM int = 3,
@FROM smalldatetime = '2022-10-05 00:00:00',
@UNTIL smalldatetime = '2022-10-06 00:00:00';
SELECT *
FROM [AdventureWorksDW2019].[dbo].[Table_2]
WHERE 1 = 1
AND (CASE WHEN @DATE_FORM = 1 THEN INFO_DATE
WHEN @DATE_FORM = 2 THEN ORDER_DATE END) BETWEEN @FROM AND @UNTIL
AND ((CASE WHEN @DATE_FORM = 3 THEN SHIPPING_DATE_START END) BETWEEN @FROM AND @UNTIL
AND (CASE WHEN @DATE_FORM = 3 THEN SHIPPING_DATE_END END) BETWEEN @FROM AND @UNTIL)
uj5u.com熱心網友回復：
它不起作用，因為您的案例不允許滿足您的條件。例如，假設@Date_Form = 3這種情況會發生什么？：
(CASE WHEN @DATE_FORM = 1 THEN INFO_DATE
WHEN @DATE_FORM = 2 THEN ORDER_DATE
END) between @FROM and @UNTIL
由于@DATE_FORM = 3未在其中處理，Case因此基本上會導致：
null between @From AND @UNTIL
這將導致錯誤..我會（為簡單起見）完全洗掉案例：
SELECT *
FROM [AdventureWorksDW2019].[dbo].[Table_2]
WHERE (@DATE_FORM = 1
AND INFO_DATE BETWEEN @FROM AND @UNTIL)
OR (@DATE_FORM = 2
AND ORDER_DATE BETWEEN @FROM AND @UNTIL)
OR (@DATE_FORM = 3
AND SHIPPING_DATE_START BETWEEN @FROM AND @UNTIL
AND SHIPPING_DATE_END BETWEEN @FROM AND @UNTIL)
uj5u.com熱心網友回復：
第一個條件：
AND (CASE WHEN @DATE_FORM = 1 THEN INFO_DATE WHEN @DATE_FORM = 2 THEN ORDER_DATE END) between @FROM and @UNTIL`
什么時候@DATE_FORM是3那AND NULL between @FROM and @UNTIL將永遠是FALSE。
FALSE因為那總是@Date_Form = 3你可能想要切換AND到的時候OR：
SELECT *
FROM [AdventureWorksDW2019].[dbo].[Table_2]
WHERE
(CASE WHEN @DATE_FORM = 1 THEN INFO_DATE
WHEN @DATE_FORM = 2 THEN ORDER_DATE END) between @FROM and @UNTIL
OR (
(CASE WHEN @DATE_FORM = 3 THEN SHIPPING_DATE_START END) between @FROM and @UNTIL
AND (CASE WHEN @DATE_FORM = 3 THEN SHIPPING_DATE_END END) between @FROM and @UNTIL
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/514576.html
標籤：sql服务器tsql