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: 
I'm pretty certain the wildcard you're trying to use for your `CASE WHEN` isn't a valid way of using it.

You can use variables to create a SQL string that you can then copy and execute - this way, if you need to increase the number of fields you're checking, you only have to change one part of your code :)

The code below creates the SQL string then prints it out in the console window:

```
DECLARE @cnt INT = 1;
DECLARE @sql VARCHAR(MAX) = 'SELECT ';
WHILE @cnt <= 10
BEGIN 
DECLARE @COLUMN VARCHAR(MAX) = 'Field' + CAST(@cnt AS varchar(max))
DECLARE @VALUE VARCHAR(MAX) = 'Value' + CAST(@cnt AS varchar(max))
SET @sql = @sql + @COLUMN + ', CASE WHEN ' + @COLUMN + '= ''AAA'' OR '  + @COLUMN + '= ''BBB'' OR ' + @COLUMN + '= ''CCC'' OR ' + @COLUMN + '= ''DDD'' OR ' + @COLUMN + '= ''EEE'' THEN 0 ELSE ' + @VALUE + ' END AS ' + @VALUE;
IF @cnt != 10 
(SELECT @sql = @sql + ',')
SET @cnt = @cnt +1
END

SET @sql = @sql + ' INTO [dbo].[LogFilter] FROM [dbo].[Log]';
PRINT @sql;

```

This code below is what is printed out (I have styled it so you can easily read it and truncated it so it doesn't take up the whole screen) :)

```
SELECT 
  Field1, 
  CASE WHEN Field1= 'AAA' OR Field1= 'BBB' OR Field1= 'CCC' OR Field1= 'DDD' OR Field1= 'EEE'
  THEN 0 ELSE Value1 END AS Value1,
  Field2, 
  CASE WHEN Field2= 'AAA' OR Field2= 'BBB' OR Field2= 'CCC' OR Field2= 'DDD' OR Field2= 'EEE' 
  THEN 0 ELSE Value2 END AS Value2,
  Field3, 
  CASE WHEN Field3= 'AAA' OR Field3= 'BBB' OR Field3= 'CCC' OR Field3= 'DDD' OR Field3= 'EEE' 
  THEN 0 ELSE Value3 END AS Value3, 
  ...
  INTO [dbo].[LogFilter] FROM [dbo].[Log]

```

This `SELECT INTO` statement above places your filtered information into a brand new table :)
Try this:

```
    Declare @Table Table(Field1 varchar(10), Value1 varchar(10),  Field2 varchar(10),  Value2 varchar(10) ,Field10 varchar(10) , Value10 varchar(10))
    Declare @NewTable Table(Field1 varchar(10), Value1 varchar(10),  Field2 varchar(10),  Value2 varchar(10) ,Field10 varchar(10) , Value10 varchar(10))

    insert into @Table
    SELECT 'AAA','9A','AAA','9A','CCC','World' Union All
    SELECT 'BBB','1','KKK','0',NULL,NULL    Union All
    SELECT 'ZZZ','9','AAA','9A','CCC','42'   Union All    
    SELECT 'CCC','7',NULL,NULL,NULL,NULL Union All
    SELECT 'LLL','9','AAA','3','III','98' Union All
    SELECT 'KKK','3','AAA','4',NULL,NULL    Union All
    SELECT 'DDD','100','AAA','Hello','CCC','6' 

    INSERT into @NewTable
    Select Field1,CASE WHEN Field1 in ('AAA','BBB','CCC','DDD','EEE') THEN 0 else Value1 end As Value1
        ,Field2,CASE WHEN Field2 in ('AAA','BBB','CCC','DDD','EEE') THEN 0 else Value2 end As Value2
        ,Field10,CASE WHEN Field10 in ('AAA','BBB','CCC','DDD','EEE') THEN 0 else Value10 end As Value10
    from @Table 

    Select * from @NewTable

```