Need to replace this inefficient CASE statement

Question

Need to replace this inefficient CASE statement

Viewing 6 posts - 16 through 20 (of 20 total)

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

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

drop proc if exists dbo.test_case_cross;
go
create proc dbo.test_case_cross
  @node_order            int,
  @Record_Key_4            nchar(1), 
  @Record_Key_5            nchar(1) 
as
with
ps_cte(popup_gid) as (
  select 1 union all select 2 union all select 3 union all select 4 union all select 5),
t_cte(node_order, Record_Key_4, Record_Key_5) as (
  select @node_order, @Record_Key_4, @Record_Key_5)
select 'Existing', *
FROM ps_cte AS PS WITH (NOLOCK)
JOIN t_cte        AS T
ON T.node_order             = 1
WHERE 1 = CASE
WHEN T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN ('I', 'X')
THEN CASE WHEN PS.popup_gid = 2 THEN 1 ELSE 0 END
ELSE
CASE WHEN PS.popup_gid = 5 THEN 1 ELSE 0 END
END
go

drop proc if exists dbo.test_case_cross_join;
go
create proc dbo.test_case_cross_join
  @node_order            int,
  @Record_Key_4            nchar(1), 
  @Record_Key_5            nchar(1) 
as
with
ps_cte(popup_gid) as (
  select 1 union all select 2 union all select 3 union all select 4 union all select 5),
t_cte(node_order, Record_Key_4, Record_Key_5) as (
  select @node_order, @Record_Key_4, @Record_Key_5)
select 'New', *
from
  ps_cte ps
 cross join 
  t_cte t
WHERE
   t.node_order=1
   and ((T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN('I', 'X')) AND  PS.popup_gid = 2)
   OR ((T.record_Key_4 <> '1' AND T.Record_Key_5 IN('I', 'X')) AND PS.popup_gid = 5)
/*
  ps.popup_gid in(2, 5)
  and t.node_order=1
  and (T.record_Key_4 = '1' or T.Record_Key_5 NOT IN ('I', 'X'))*/
go

exec dbo.test_case_cross 1, '1', 'Y';
exec dbo.test_case_cross 1, '1', 'X';
exec dbo.test_case_cross 1, '4', 'Y';
exec dbo.test_case_cross 1, '4', 'I';

exec dbo.test_case_cross_join 1, '1', 'Y';
exec dbo.test_case_cross_join 1, '1', 'X';
exec dbo.test_case_cross_join 1, '4', 'Y';
exec dbo.test_case_cross_join 1, '4', 'I';

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

ChrisM@Work wrote:

WHERE 1 = CASE
WHEN T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN('I', 'X') THEN CASE WHEN PS.popup_gid = 2 THEN 1 eLSE 0 END
ELSE CASE WHEN PS.popup_gid = 5 THEN 1 ELSE 0 END
END;
This works for me.

Yes, I'm not sure why the OP thinks it's inefficient?

Steve Collins SSCrazy Points: 2157 More actions · Answer 3

I agree. The answers are equivalent at best.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

VoldemarG Hall of Fame Points: 3883 More actions · Answer 4

here is Create Table DDL.

Attachments:

You must be logged in to view attached files.

Likes to play Chess

VoldemarG Hall of Fame Points: 3883 More actions · Answer 5

THIS works:

WHERE ((T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN('I', 'X')) AND PS.popup_gid = 2)

OR ((T.record_Key_4 <> '1' AND T.Record_Key_5 IN('I', 'X')) AND PS.popup_gid = 5)

thanks for your help !!!

Likes to play Chess

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

VoldemarG wrote:

THIS works:
WHERE ((T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN('I', 'X')) AND PS.popup_gid = 2)
OR ((T.record_Key_4 <> '1' AND T.Record_Key_5 IN('I', 'X')) AND PS.popup_gid = 5)
thanks for your help !!!

Just an aside, this is logically different to your original post and could return different results depending on your data.

Your OP was concerned with performance - is this faster?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden