sql - case inside where 子句包含 db2 中的 where 条件

Question

我有一个用例，我需要编写一个查询，该查询在 where 子句中包含 case 语句，条件如下：我在“SAMPLE_TABLE”中有一个名为“BIRTHDATE”的列 1）如果仅来自日期（这是一个参数，即：来自）给出，那么我需要从 SAMPLE_TABLE 中获取其 BIRTHDATE >= :from 2) 的记录，如果仅给出迄今为止（这是一个参数，即 :to），然后获取 BIRTHDATE <= :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)

请提供一个有效的查询。提前致谢。

标签： sqldb2db2-luw

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)