combination of if statements - 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)

combination of if statements

Author

Topic

j-in-nz
Starting Member

17 Posts

Posted - 2007-04-01 : 18:47:11

IS there a better way of checking for combination of conditions than case statements like this.
CASE WHEN (@K1 = 1 and @K2 = 1 and @K3 = 1 and @K4 = 1) THEN --4 kpi's setup
CASE
WHEN (@UserKPI1>0 and @UserKPI2>0 and @UserKPI3>0 and @UserKPI4>0) THEN 1
ELSE 0
END
WHEN (@K1 = 1 and @K2 = 1 and @K3 = 1 and @K4 = 0) THEN --3 kpi's setup
CASE
WHEN (@UserKPI1>0 and @UserKPI2>0 and @UserKPI3>0) THEN 1
ELSE 0
END
WHEN (@K1 = 1 and @K2 = 1 and @K3 = 0 and @K4 = 0) THEN --2 kpi's setup
CASE
WHEN (@UserKPI1>0 and @UserKPI2>0) THEN 1
ELSE 0
END
WHEN (@K1 = 1 and @K2 = 0 and @K3 = 0 and @K4 = 0) THEN --1 kpi setup
CASE
WHEN (@UserKPI1>0 and @UserKPI2=0) THEN 1
ELSE 0
END
WHEN (@K1 = 0 and @K2 = 0 and @K3 = 0 and @K4 = 0) THEN --no kpi's setup
CASE
WHEN (@UserKPI1=0 and @UserKPI2=0 and @UserKPI3=0 and @UserKPI4=0) THEN 1
ELSE 0
END

END

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-01 : 22:15:24

this does the same. your call on what's more readable/maintainable.


CASE 
	WHEN (@K1 = 1 and @K2 = 1 and @K3 = 1 and @K4 = 1 and @UserKPI1>0 and @UserKPI2>0 and @UserKPI3>0 and @UserKPI4>0) OR
             (@K1 = 1 and @K2 = 1 and @K3 = 1 and @K4 = 0 and @UserKPI1>0 and @UserKPI2>0 and @UserKPI3>0) OR
             (@K1 = 1 and @K2 = 1 and @K3 = 0 and @K4 = 0 and @UserKPI1>0 and @UserKPI2>0) OR
             (@K1 = 1 and @K2 = 0 and @K3 = 0 and @K4 = 0 and @UserKPI1>0 and @UserKPI2=0) OR
             (@K1 = 0 and @K2 = 0 and @K3 = 0 and @K4 = 0 and @UserKPI1=0 and @UserKPI2=0 and @UserKPI3=0 and @UserKPI4=0) THEN 1
	ELSE 0
END

www.elsasoft.org

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-04-03 : 02:02:31

A simple bit of binary math will make the CASE a lot easier... of course, this depends on your original query having a bit of a flaw in that you did not consider all of the @User variables in each condition. If that's not a flaw, then you can still use the @KT portion of the code but you will need to use the original code where the @User variables are concerned... I'm also making the horrible assumption (only you would know) that none of the @User variables will ever be less than zero...

SET @KT = @K1+(@K2*2)+(@K3*4)+(@K4*8)
SET @UT = SIGN(@UserKPI1)+SIGN(@UserKPI2)*2+SIGN(@UserKPI3)*4+SIGN(@UserKPI4)*8
. . .
. . .
CASE WHEN (@KT=15 and @UT = 15)
       OR (@KT= 7 and @UT =  7) 
       OR (@KT= 3 and @UT =  3) 
       OR (@KT= 1 and @UT =  1) 
       OR (@KT= 0 and @UT =  0) 
     THEN 1
     ELSE 0
END

--Jeff Moden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 02:08:52

Nice! I like that solution!

However, beware of negative numbers for @Kx and @UserKPIx variables.
Add an ABS too, just in case.

Peter Larsson
Helsingborg, Sweden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-03 : 02:14:25

it's a cute solution, BUT:

I would say that kind of trick requires copious comments.

otherwise in 6 months you come back and wonder "what the hell was I trying to do here??"

www.elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 02:37:00

Riding Jeff's suggestion...

SET @KT = SIGN(ABS(@K1)) + 2*SIGN(ABS(@K2)) + 4*SIGN(ABS(@K3)) + 8*SIGN(ABS(@K4))
SET @UT = SIGN(ABS(@UserKPI1)) + 2*SIGN(ABS(@UserKPI2)) + 4*SIGN(ABS(@UserKPI3)) + 8*SIGN(ABS(@UserKPI4))
. . .
. . .
WHERE @KT ^ @UT = 0
. . .
. . .
WHERE @KT = @UT AND @KT IN (0, 1, 3, 7, 15)

Peter Larsson
Helsingborg, Sweden

j-in-nz
Starting Member

17 Posts

Posted - 2007-04-03 : 04:57:28

Hi

Thank you for the responses.

Will that math also cover the cases below:
(@K1 = 1 and @K2 = 0 and @K3 = 0 and @K4 = 1) or
(@K1 = 0 and @K2 = 1 and @K3 = 0 and @K4 = 1) or
(@K1 = 1 and @K2 = 0 and @K3 = 1 and @K4 = 0) or

I basically want to cover all combinations of these fields.

thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 05:08:49

If you want all combinations and both values must be equal, use

WHERE @KT = @UT

Peter Larsson
Helsingborg, Sweden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-04-04 : 08:32:05

quote:
Originally posted by jezemine

it's a cute solution, BUT:

I would say that kind of trick requires copious comments.

otherwise in 6 months you come back and wonder "what the hell was I trying to do here??"

www.elsasoft.org

I thought it was a given that production code should be documented well enough that you could remove all code and still be able to build a functional flow chart that you could figure out how to rewrite the code just from those comments. Jezemine is absolutely correct... I've run into folks that couldn't tell the difference between a bit mask and a Halloween mask

... and, considering the complexity of the original WHERE clause, I'd hope there would be some adequate documentation even before the trick with the bit mask creation and comparison.

--Jeff Moden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-04 : 11:42:47

I would take a completely different approach. It looks like you can easily store this logic in a table somewhere and simply query it to get the result that you need. Right now, as written and without any info provided, it is impossible to guess what you actually need. But I suspect that if you explain your logic to us and what this all means, we can help give you a much better, more flexible and easier to work with solution.

Always try to avoid storing DATA in your SQL code, which is what you are doing here. If you can take this logic out and create a table and store it there, it will be clearer, easier to maintain, and shorter.

(see http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx for more on this general concept).

- Jeff
http://weblogs.sqlteam.com/JeffS

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-04-04 : 22:00:17

quote:
Originally posted by jsmith8858

I would take a completely different approach. It looks like you can easily store this logic in a table somewhere and simply query it to get the result that you need. Right now, as written and without any info provided, it is impossible to guess what you actually need. But I suspect that if you explain your logic to us and what this all means, we can help give you a much better, more flexible and easier to work with solution.

Always try to avoid storing DATA in your SQL code, which is what you are doing here. If you can take this logic out and create a table and store it there, it will be clearer, easier to maintain, and shorter.

(see http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx for more on this general concept).

- Jeff
http://weblogs.sqlteam.com/JeffS

--Jeff Moden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-05 : 08:15:57

??

- Jeff
http://weblogs.sqlteam.com/JeffS

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-05 : 14:22:38

I think Jeff is trying to plagiarize Jeff.

www.elsasoft.org

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-05 : 14:29:35

I once had a consultant tell me that it was a complicated problem to code because he needed to code in boolean logic...I almost dropped him right there

KISS

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-04-05 : 18:19:25

Heh... sorry about that... dunno why my comments didn't "take".

What I wanted to say is that this is some fairly simple boolean algebra and I don't think an external table is necessary.

--Jeff Moden

j-in-nz
Starting Member

17 Posts

Posted - 2007-04-05 : 18:59:59

I agree 100% jeff. The sp is passing values to validate what values need to be eventually stored in a table......no point storing the passed values too.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-05 : 19:02:05

you're both missing the point of what I was saying, I think. Again, without more details about what you are doing, who knows what the best solution is. But look at the link I gave you; all of those situations are "simple boolean expressions" as well, but all of those situations are times when the data should be properly stored in tables, not code.

- Jeff
http://weblogs.sqlteam.com/JeffS

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-04-05 : 22:58:33

What would you store in a table, in this case? The numbers 0 through 15?

--Jeff Moden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-06 : 08:22:03

I will repeat yet again:

Without more specific details regarding what he is doing, we don't know for sure.

Right now we only have convoluted expression that hasn't been explained at all in terms of what the algorithm is, or what the inputs are or what the outputs mean. Who knows what the best solution is?

All I am saying is (repeating yet again): It looks like it is possible that it may be easier, shorter and cleaner to potentially store some data in a table somewhere to replace this calculation, thereby keeping the data in the tables and out of the code, which we can determine if we get more information. Read the article I linked to for very simple (hopefully easy to follow) examples.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources