SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL : Problem with WHEN ... CASE


T-SQL : Problem with WHEN ... CASE

Author
Message
Valbuenito
Valbuenito
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 40
Hi,

I have a little problm.. or not Smile
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 Smile
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16054 Visits: 18627
Quick thought, use the NULLIF function
Cool
USE tempdb;
GO

DECLARE @TEST TABLE (STR_VAL VARCHAR(50) NOT NULL);
INSERT INTO @TEST(STR_VAL) VALUES ('ABC'),('DEF'),('NULL'),('JKL'),('NULL');

SELECT
T.STR_VAL
,NULLIF(T.STR_VAL,'NULL') AS STR_VAL_NULL
FROM @TEST T



Results
STR_VAL  STR_VAL_NULL
-------- ------------
ABC ABC
DEF DEF
NULL NULL
JKL JKL
NULL NULL

Valbuenito
Valbuenito
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 40
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
Eirikur Eiriksson
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16054 Visits: 18627
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
Cool
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 TABLE (ID INT NOT NULL, CODE VARCHAR(50) NULL, VALUE1 VARCHAR(50) NULL);
INSERT INTO @TEST(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 T
INNER JOIN @PARAM P
ON T.CODE = P.PARAM_VAL;


Sean Pearce
Sean Pearce
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1848 Visits: 3432
SELECT ID, Code, Value1
FROM Table1 T1
WHERE ISNULL(T1.[Code], 'NULL') IN (@Param)





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Valbuenito
Valbuenito
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 40
Hi Eirikur Eiriksson,

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

But Thanks Smile

Sean Pearce, I like you !
You're soultion is good and work fot my exemple !

Thanks Smile
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5696 Visits: 6900
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search