问题描述
我有一个 where
子句,我需要在其中添加 case
语句,并在 then
和 else
内部必须给出条件语句.
I have a where
clause in which i need to add case
statement and inside then
and else
i have to give conditional statement.
我的状况看起来像这样
WHERE
CASE
WHEN
Substring(datename(dw,getdate()),1,3) = 'mon'
THEN convert(varchar,dt_start,103) = convert(varchar,dateadd(day,-3,getdate()),103)
AND convert(varchar,dt_start,103) <= convert(varchar,dateadd(day,-2,getdate()),103)
ELSE convert(varchar,dt_start,103) = convert(varchar,dateadd(day,-1,getdate()),103)
END
但是此查询在 then
中的 =
处显示语法错误.相同的逻辑在PostgreSQL中有效,但是当我在SQL Server中尝试时,它在=处语法错误.我如何解决这个问题.请帮助
这是相应的PGSQL查询.
But this query is showing syntax error at =
inside then
. The same logic works in PostgreSQL but when i tried in SQL server it has got syntax error at =. How i can solve this. Please help
This is the corresponding PGSQL query.
WHERE
CASE
WHEN to_char(now(), 'dy'::text) = 'mon'::text THEN to_char(dt_start, 'yyyymmdd'::text) = to_char(now() - '3 days'::interval, 'yyyymmdd'::text) AND to_char(dt_start, 'yyyymmdd'::text) <= to_char(now() - '2 days'::interval, 'yyyymmdd'::text)
ELSE to_char(dt_start, 'yyyymmdd'::text) = to_char(now() - '1 day'::interval, 'yyyymmdd'::text)
END
推荐答案
尝试:您必须根据自己的要求设置两个单独的条件并记住其中一项,只能启动 CASE
作为 WHERE a = CASE
而不是 WHERE CASE ... THEN a = b + c ...
或在这种情况下,我建议您使用动态查询,在这种情况下,根据不同条件操作查询非常容易
TRY THIS: You have to make two separate conditions based on your requirement and remember onething, you can only start CASE
as WHERE a = CASE
not as WHERE CASE ...THEN a = b+c ...
or in such cases I would suggest to go with dynamic queries where it's quite easy to manipulate the queries based on the different conditions
WHERE convert(varchar,dt_start,103) =
CASE WHEN Substring(datename(dw,getdate()),1,3) = 'mon' THEN
convert(varchar,dateadd(day,-3,getdate()),103)
ELSE
convert(varchar,dateadd(day,-1,getdate()),103)
END
AND convert(varchar,dt_start,103) <=
CASE WHEN Substring(datename(dw,getdate()),1,3) = 'mon' THEN
convert(varchar,dateadd(day,-2,getdate()),103)
ELSE
convert(varchar,dt_start,103)
END
这篇关于在where条件中添加case语句,然后在then子句中提供条件语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!