Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T-SQL : Problem with WHEN ... CASE Expand / Collapse
Author
Message
Posted Wednesday, July 16, 2014 2:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:51 AM
Points: 11, Visits: 27
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 :)
Post #1592942
Posted Wednesday, July 16, 2014 2:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 2,395, Visits: 6,616
Quick thought, use the NULLIF function

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
Post #1592945
Posted Wednesday, July 16, 2014 2:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:51 AM
Points: 11, Visits: 27
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.
Post #1592955
Posted Wednesday, July 16, 2014 3:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 2,395, Visits: 6,616
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 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;


Post #1592965
Posted Wednesday, July 16, 2014 4:58 AM This worked for the OP Answer marked as solution


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:11 AM
Points: 969, Visits: 3,006
SELECT ID, Code, Value1
FROM Table1 T1
WHERE ISNULL(T1.[Code], 'NULL') IN (@Param)





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1592988
Posted Wednesday, July 16, 2014 6:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:51 AM
Points: 11, Visits: 27
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 :)
Post #1593013
Posted Thursday, July 24, 2014 12:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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? -- Stephen Stills
Post #1595795
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse