Question: I have a SQL query with a case statement. The case statement has a subquery. I want to replace (delete) the subselect with a join. It is possible? How can i do it?

The Query:

```
SELECT tablex.Abnumber, CASE WHEN Abdate is not null AND isnull(Abnumber,0) > 1 AND Abdate < (SELECT Abdate FROM t_tablex WHERE Annumber = @Annumber AND Abnumber= @Abnumber-1) THEN 'bla bla' ELSE '' END
FROM t_tablex AS tablex
WHERE (@Annumber IS NULL OR tablex.Annumber= @Annumber)
  AND (@AbnumberIS NULL OR tablex.Abnumber= @Abnumber)

```

Answer: 
If I understood correctly, you are trying to update ValueX columns based on FieldX data.
In that case you need to write Case condition for every column.

You can directly write 10 Case conditions like below

```
UPDATE A_LOG 
SET VALUE1 = CASE WHEN FIELD1 IN ('AAA','BBB','CCC','DDD','EEE' ) THEN 0 ELSE VALUE1 END,
VALUE2 = CASE WHEN FIELD2 IN ('AAA','BBB','CCC','DDD','EEE' ) THEN 0 ELSE VALUE2 END,
VALUE3 = CASE WHEN FIELD3 IN ('AAA','BBB','CCC','DDD','EEE' ) THEN 0 ELSE VALUE3 END
.
.
VALUE10 = CASE WHEN FIELD10 IN ('AAA','BBB','CCC','DDD','EEE' ) THEN 0 ELSE VALUE10 END

```

Or you can generate Update statement with Dynamic query like below

```
DECLARE @IN VARCHAR(100)=' IN (''AAA'',''BBB'',''CCC'',''DDD'',''EEE'' )'
, @SQL VARCHAR(MAX) =  'UPDATE A_LOG SET '

SELECT @SQL = @SQL+  VAL FROM 
(
SELECT    COLUMN_NAME + ' = CASE WHEN FIELD'+ REPLACE(COLUMN_NAME,'VALUE','')
+ @IN+ ' THEN 0 ELSE '+ COLUMN_NAME + ' END,
' VAL
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'LOG'
AND COLUMN_NAME LIKE 'VALUE%'
)A

SELECT @SQL = SUBSTRING(@SQL,1,LEN(@SQL)-3)
SELECT @SQL

```

That will give you update statement for every column with a case condition.
The Case statement wouldn't work in the way that you've written. You need to individually compare the values in each Field and set the value of 'Value' column.

```
SELECT CAST(
             CASE 
                  WHEN Field1 = AAA or BBB or CCC or DDD or EEE 
                     THEN Value1 = 0                     
                  ELSE Value1
             END AS bit) Value1,
        CAST(
             CASE 
                  WHEN Field2 = AAA or BBB or CCC or DDD or EEE 
                     THEN Value2 = 0                     
                  ELSE Value2
             END AS bit) Value2,
        .    
        .
        .
        .
        <need to write separate case statement for each column which is to be compared>
FROM dbo.Log

```