where子句中的case，该子句包含db2中的where条件

Question

我有一个用例，需要在where子句中编写一个包含case语句的查询，条件如下：我在"SAMPLE_TABLE"中有一个名为"BIRTDATE"的列1( 如果只给定了from date(这是一个参数，即：from(，那么我需要从其出生日期>=：from的SAMPLE_TABLE中获取记录2( 如果只给定了截止日期(这是一个参数，即：to(，则获取其出生日期<=的记录：到3( 如果同时给出了开始日期和结束日期，则获取这些日期之间的记录。

以下是我尝试的查询。但无法找到解决方案。

SELECT BIRTHDATE FROM SAMPLE_TABLE
WHERE
(CASE
WHEN (:from AND NOT :to)
THEN BIRTHDATE >= :receivefrom
WHEN (:to AND NOT :from)
THEN BIRTHDATE <= :to
WHEN (:to AND :from)
THEN BIRTHDATE BETWEEN :from AND :to
END)

请提供一个工作查询。提前谢谢。

Answer 1

当不包括日期时，此代码会将其留空。这在SQL Server 中进行了测试

declare @from date='1998-12-07'
declare @to date ='2000-12-07'
SELECT [Birthdate] --when only from is available
FROM SAMPLE_TABLE
where (case when (@to='' and  @from !='') then 1 else 0  end)=1
and BIRTHDATE >= @from
UNION
SELECT [Birthdate] --when only to is available
FROM SAMPLE_TABLE
where (case when (@to!='' and  @from ='') then 1 else 0  end)=1
and BIRTHDATE <= @to
UNION
SELECT [Birthdate] --when both from and to are avilable
FROM SAMPLE_TABLE
where (case when (@to!='' and  @from !='') then 1 else 0  end)=1
and BIRTHDATE between @from and @to

或者你可以试试这个

declare @from date='1998-12-07'
declare @to date ='2000-12-07'
IF (@from!='' and @to!='')
SELECT [Birthdate] 
FROM SAMPLE_TABLE
Where [Birthdate] between @from and @to 
ELSE IF (@from='' and @to!='')
(SELECT [Birthdate] 
FROM SAMPLE_TABLE
Where [Birthdate]<= @to)
ELSE IF (@from!='' and @to='')
(SELECT [Birthdate] 
FROM SAMPLE_TABLE
Where [Birthdate]>= @from)

Answer 2

我想你只是想要：

SELECT BIRTHDATE
FROM SAMPLE_TABLE
WHERE (BIRTHDATE >= :from OR :from IS NULL) AND
(BIRTHDATE <= :to OR :to IS NULL)

Answer 3

我假设如果不应该传递某个参数，那么它的值为NULL。

where birthdate between coalesce(:from, date('0001-01-01')) and coalesce(:to, date('9999-12-31'))