在where条件中添加case语句

在where条件中添加case语句

本文介绍了在where条件中添加case语句,然后在then子句中提供条件语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

08-19 16:12