T-SQL : Problem with WHEN ... CASE

Question

Post reply

T-SQL : Problem with WHEN ... CASE

Valbuenito

SSC-Addicted

Points: 416
More actions
July 16, 2014 at 2:08 am

#397064

Hi,
I have a little problm.. or not 🙂
On my database, I have many NULL values and when I run my request, I have a result without the NULL values
My SQL request :
SELECT ...
FROM Table1 T1
WHERE T1.[Code] IN (@Param)
On my @Param, I have many values : ('001','002','003','NULL').
I have the text value 'NULL'.
I would like have a transformation of my text value 'NULL' on NULL type.
I try that, but don't work :
SELECT ...
FROM Table1 T1
WHERE T1.[Code] IN (@Param) or CASE WHEN FRH.[Resource Code] LIKE '%NULL%' THEN NULL)
An idea ?
Thanks 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 1

Quick thought, use the NULLIF function

😎

USE tempdb;

GO

DECLARE @test-2 TABLE (STR_VAL VARCHAR(50) NOT NULL);

INSERT INTO @test-2(STR_VAL) VALUES ('ABC'),('DEF'),('NULL'),('JKL'),('NULL');

SELECT

T.STR_VAL

,NULLIF(T.STR_VAL,'NULL') AS STR_VAL_NULL

FROM @test-2 T

Results

STR_VAL STR_VAL_NULL

-------- ------------

ABC ABC

DEF DEF

NULL NULL

JKL JKL

NULL NULL

Valbuenito SSC-Addicted Points: 416 More actions · Answer 2

Hi Eirikur,

Thanks for your reply.

But, I do not think that answers my problem because I don't master on my parameter.

This request comes of Reporting, and the parameter is a choice of many propositions.

That is why I would have a condition in the when condition.

My table :

ID CODE VALUE1

----- -------- ------------

01 C001 ABC

02 C002 DEF

03 NULL NULL

04 NULL JKL

05 NULL NULL

My request of my Reporting:

SELECT ID, Code, Value1

FROM Table1 T1

WHERE T1.[Code] IN (@Param)

The ALL Choice Of @Param of my Reporting : ALL, C001, C002, 'NULL'

If @Param = ('C001','C002), it's OK. The result is :

ID CODE VALUE1

----- -------- ------------

01 C001 ABC

02 C002 DEF

But If @Param = ALL (ALL = ('C001','C002','NULL'), it son't work. The NULL type isn't returned :

ID CODE VALUE1

----- -------- ------------

01 C001 ABC

02 C002 DEF

So, I search a means to look my lines with an NULL Code when my parameter is filter on ALL.

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 3

Valbuenito (7/16/2014)
Hi Eirikur,
Thanks for your reply.
But, I do not think that answers my problem because I don't master on my parameter.
This request comes of Reporting, and the parameter is a choice of many propositions.
That is why I would have a condition in the when condition.
My table :
ID CODE VALUE1
----- -------- ------------
01 C001 ABC
02 C002 DEF
03 NULL NULL
04 NULL JKL
05 NULL NULL
My request of my Reporting:
SELECT ID, Code, Value1
FROM Table1 T1
WHERE T1.[Code] IN (@Param)
The ALL Choice Of @Param of my Reporting : ALL, C001, C002, 'NULL'
If @Param = ('C001','C002), it's OK. The result is :
ID CODE VALUE1
----- -------- ------------
01 C001 ABC
02 C002 DEF
But If @Param = ALL (ALL = ('C001','C002','NULL'), it son't work. The NULL type isn't returned :
ID CODE VALUE1
----- -------- ------------
01 C001 ABC
02 C002 DEF
So, I search a means to look my lines with an NULL Code when my parameter is filter on ALL.

There is a flaw in the code

😎

SELECT ID, Code, Value1

FROM Table1 T1

WHERE T1.[Code] IN (@Param)

Unless the @Param is a table variable used in either a nested query or a join, it will only work with a single parameter.

Here is a more correct example

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @PARAM TABLE (PARAM_VAL VARCHAR(50));

DECLARE @test-2 TABLE (ID INT NOT NULL, CODE VARCHAR(50) NULL, VALUE1 VARCHAR(50) NULL);

INSERT INTO @test-2(ID,CODE,VALUE1) VALUES (1,'C001','ABC'),(2,'C002','DEF'),(3,'NULL','NULL'),(4,'NULL','JKL'),(5,'NULL','NULL');

INSERT INTO @PARAM(PARAM_VAL) VALUES ('C001'),('C002'),('NULL');

SELECT

T.ID

,T.CODE

,T.VALUE1

FROM @test-2 T

INNER JOIN @PARAM P

ON T.CODE = P.PARAM_VAL;

Sean Pearce SSCoach Points: 15999 More actions · Answer 4

SELECT ID, Code, Value1

FROM Table1 T1

WHERE ISNULL(T1.[Code], 'NULL') IN (@Param)

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Valbuenito SSC-Addicted Points: 416 More actions · Answer 5

Hi Eirikur Eiriksson,

Thanks for your solution but with my Reporting it's not appropriate to create a Temporary table

But Thanks 🙂

Sean Pearce, I like you !

You're soultion is good and work fot my exemple !

Thanks 🙂

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

Sean Pearce (7/16/2014)
SELECT ID, Code, Value1 FROM Table1 T1 WHERE ISNULL(T1.[Code], 'NULL') IN (@Param)

One thing about the code above. It is always going to produce a table scan, because it is wrapping a function around the [Code] column. If the table is a large one this may run slowly. If the table has a covering index on the Code column, a faster performing solution would be to join the table variable to the table. This would return everything but the rows where the Code value is null. You could use a UNION ALL to get the NULLS like this.

UNION ALL

SELECT ID, Code, Value1

From Table T1

Where 'NULL' in (@param) and T1.Code is null

Again, this code runs faster if a covering index on the code column exists, and if you are trying to pull a few rows out of a big table. The more rows your @Param will pull, the more likely that the optimizer will choose to do a table scan anyway, but it's important to know that functions wrapped around columns are not "SARGABLE." If you want to know more, just search on that word. It will pay off for you in the future.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills