Using a Conditional in a T-SQL WHERE Clause

Question

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.

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.

(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

Incorrect syntax near the keyword 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.

Aside: Are you trying to use MAX on a string containing an integer value? Do you expect the result to be the alphabetical, not numeric, maximum? — HABO
– HABO, Commented Jul 23, 2015 at 2:23

jpw · Accepted Answer · 2015-07-23 00:07:06Z

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?

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
)

answered Jul 23, 2015 at 0:07

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Over a year ago

That looks like it did it. I'm still getting an error about the subquery returning more than one value, but this is progress. I do know that both cases do the same thing. I was just trying to get this part to work before I began modifying the ELSE to do what I need it to do. Little steps. :)

Eduardo Molteni · Accepted Answer · 2015-07-22 23:36:16Z

0

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

Use CASE or IIF instead

answered Jul 22, 2015 at 23:36

Eduardo Molteni

39.5k25 gold badges145 silver badges212 bronze badges

1 Comment

Tim Over a year ago

I have tried using case as well, and still got an error.

dean · Accepted Answer · 2015-07-22 23:37:29Z

0

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?

answered Jul 22, 2015 at 23:37

dean

3652 gold badges3 silver badges10 bronze badges

Collectives™ on Stack Overflow

Using a Conditional in a T-SQL WHERE Clause

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related