在T-SQL WHERE子句中使用条件

I have an existing T-SQL query that returns a list of customers who need to be sent updates. As a part of the WHERE clause, the query checks to make sure the version conforms to a length of 6, and then appends additional characters.

我有一个现有的T-SQL查询,它返回需要发送更新的客户列表。作为WHERE子句的一部分,查询将检查以确保版本符合长度6,然后附加其他字符。

Our latest version has a longer version, so I need to add a conditional statement to the SQL, but I keep getting an error when I do so. I won't post the entire statement for brevity. The SQL statement returns values when I run it like this.

我们的最新版本有更长的版本,所以我需要在SQL中添加一个条件语句,但是当我这样做时我一直都会收到错误。为简洁起见,我不会发布整个声明。当我像这样运行它时,SQL语句返回值。

(SELECT     
     MAX(substring(cversion, 1, 4) + case when len(cversion) = 6 then '0' else '' end + substring(cversion,5,3))
 FROM version
 GROUP BY iproductid, LEFT(cversion, 3)))

Below are my modifications. I know that the else code is identical, but I am just trying to get it to run currently. All I did was add the CASE and ELSE, and duplicate what was done, but now I get errors stating

以下是我的修改。我知道else代码是相同的,但我只是想让它当前运行。我所做的只是添加CASE和ELSE,并复制已完成的内容,但现在我收到错误说明

Incorrect syntax near the keyword SELECT

关键字SELECT附近的语法不正确

along with two similar errors near the added keywords.

以及添加的关键字附近的两个类似错误。

vr.cversion IN
(
    CASE WHEN product.iproductid < 8 THEN
        SELECT MAX
        (
            substring(cversion,1,4) + 
                case when len(cversion) = 6 
                    then '0' 
                    else '' 
                end + substring(cversion,5,3)
        )
        FROM version
        GROUP BY iproductid,LEFT(cversion, 3)
    ELSE
            SELECT MAX
        (
            substring(cversion,1,4) + 
                case when len(cversion) = 6 
                    then '0' 
                    else '' 
                end + substring(cversion,5,3)
        )
        FROM version
        GROUP BY iproductid,LEFT(cversion, 3)
    END
)

Could anyone tell me what I am doing wrong? Thanks.

谁能告诉我我做错了什么?谢谢。

UPDATE

Several people have said I need to use CASE instead. I had tried that with the same results, but I updated the code to show how I organized it.

有几个人说过我需要使用CASE。我尝试过相同的结果,但我更新了代码以显示我是如何组织它的。

3 个解决方案

#1

You need parentheses around the select statements so that they get parsed as sub-queries. I noticed that the two code blocks are identical, but I guess they are meant to not be?

您需要围绕select语句使用括号,以便将它们解析为子查询。我注意到两个代码块是相同的,但我猜他们不是这样的?

vr.cversion IN 
(
    CASE WHEN product.iproductid < 8 THEN
        (
          SELECT MAX
          (
             substring(cversion,1,4) + 
                case when len(cversion) = 6 
                    then '0' 
                    else '' 
                end + substring(cversion,5,3)
          )
          FROM version
          GROUP BY iproductid,LEFT(cversion, 3)
       )
    ELSE
        (
           SELECT MAX
          (
             substring(cversion,1,4) + 
                case when len(cversion) = 6 
                    then '0' 
                    else '' 
                end + substring(cversion,5,3)
          )
          FROM version
          GROUP BY iproductid,LEFT(cversion, 3)
       )
    END
)

#2

You can't use IF inside a TSQL Statement. You can only use it on Stored Procedures.

您不能在TSQL语句中使用IF。您只能在存储过程中使用它。

Use CASE or IIF instead

请改用CASE或IIF

#3

When you start that IN query you start it with if - shouldn't you start that with a CASE statement? CASE When THEN ELSE END?

当你启动IN查询时,你启动它if - 你不应该用CASE语句启动吗?情况那么结束了吗?

#1