complex CASE statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

complex CASE statement

Author

Topic

j-in-nz
Starting Member

17 Posts

Posted - 2007-03-19 : 22:41:35

Hi,
The logic of the case statement below makes sense but it does not seem to work fine. In my tests when review is 1/2 I get a null value instead of the proper date value. Do I had IF statements within the CASE instead of more CASE statements?

UPDATE table
SET FIELD1 =
CASE
WHEN STATUS = 0 THEN NULL
WHEN STATUS = 1 or STATUS = 2 THEN
CASE
WHEN AdditionalInfoRequested = 1 THEN DateAddInfoReceived
WHEN AdditionalInfoRequested = 2 THEN
CASE
WHEN @Review = 1 THEN Date1
WHEN @Review = 2 THEN Date2
END
ELSE NULL
END
END

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-03-20 : 03:49:27

No you can not use If in Queries.

If you can post some sample data then may be some one over here can help you ..

Chirag

http://chirikworld.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 04:13:34

Something like this?

UPDATE	table
SET	Field1 =	CASE
				WHEN STATUS IN (1, 2) AND AdditionalInfoRequested = 1 THEN DateAddInfoReceived
				WHEN STATUS IN (1, 2) AND AdditionalInfoRequested = 2 AND @Review = 1 THEN Date1
				WHEN STATUS IN (1, 2) AND AdditionalInfoRequested = 2 AND @Review = 2 THEN Date2
				ELSE NULL
			END

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-20 : 04:26:24

This may perform slightly better:

UPDATE	table
SET	Field1 =	NULL
Where
	STATUS = 0

UPDATE	table
SET	Field1 =	CASE
				WHEN AdditionalInfoRequested = 1 THEN DateAddInfoReceived
				WHEN AdditionalInfoRequested = 2 AND @Review = 1 THEN Date1
				WHEN AdditionalInfoRequested = 2 AND @Review = 2 THEN Date2
				ELSE NULL
			END
Where
	STATUS IN (1, 2)

Remember, avoid the use of CASE when working on large data.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 04:42:54

Even faster?

UPDATE	Table
SET	Field1 = NULL
WHERE	AdditionalInfoRequested NOT IN (1, 2)

UPDATE	table
SET	Field1 = DateAddInfoReceived
WHERE	Status IN (1, 2)
	AND AdditionalInfoRequested = 1

UPDATE	table
SET	Field1 = Date1
WHERE	Status IN (1, 2)
	AND AdditionalInfoRequested = 2
	AND @Review = 1

UPDATE	table
SET	Field1 = Date2
WHERE	Status IN (1, 2)
	AND AdditionalInfoRequested = 2
	AND @Review = 2

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-20 : 04:48:03

Not really.

Since you used NOT IN, first query is going to perform badly.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 05:04:22

Ok...

What about this one?

UPDATE	Table
SET	Field1 = NULL

UPDATE	table
SET	Field1 = DateAddInfoReceived
WHERE	Status IN (1, 2)
	AND AdditionalInfoRequested = 1

UPDATE	table
SET	Field1 = Date1
WHERE	Status IN (1, 2)
	AND AdditionalInfoRequested = 2
	AND @Review = 1

UPDATE	table
SET	Field1 = Date2
WHERE	Status IN (1, 2)
	AND AdditionalInfoRequested = 2
	AND @Review = 2

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-20 : 06:34:46

Yes..that is much better. But don't you think @Review = 1 will be checked for each row? Rather than that, using IF will be much efficient.

Of course, I am assuming there are proper indexes on the columns in the WHERE clause. Else this discussion has no meaning.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 07:08:51

This can go on whole day...

UPDATE	Table
SET	Field1 = NULL

UPDATE	table
SET	Field1 = DateAddInfoReceived
WHERE	Status IN (1, 2)
	AND AdditionalInfoRequested = 1

IF @Review = 1
	UPDATE	table
	SET	Field1 = Date1
	WHERE	Status IN (1, 2)
		AND AdditionalInfoRequested = 2

IF @Review = 2
	UPDATE	table
	SET	Field1 = Date2
	WHERE	Status IN (1, 2)
		AND AdditionalInfoRequested = 2

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-20 : 07:29:22

I don't know whether this final version is going to please the OP, but I am sure its much better than original CASE version. Of course, it's a question of trade off between speed vs. maintainability.

Thanks Peter...

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources