在where条件中添加case语句，然后在then子句中提供条件语句 [英] Adding case statement inside where condition and provide conditional statements inside then clause

问题描述

我有一个 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子句中提供条件语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！