Compare Fields in select - 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 2000 Forums

Transact-SQL (2000)

Compare Fields in select

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2004-09-03 : 10:25:38

I need to do the following:

SELECT SkillsRequiredCount,
MySkillsCount,
(get a bool value "1" if SkillsRequiredCount=MySkillsCount else must be "0") as SkillsMatch

FROM

.......

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-03 : 10:27:42

will this do?

select case when SkillsRequiredCount=MySkillsCount then 1 else 0 end as SkillsMatch
from ...

Go with the flow & have fun! Else fight the flow :)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-03 : 10:28:34

SELECT SkillsRequiredCount, MySkillsCount, CASE WHEN SkillsRequiredCount = MySkillsCount THEN 1 ELSE 0 END as SkillsMatch
FROM ......

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-03 : 10:30:02

Damn Spirit1 you beat me to it again.
Do you ever get any work done? - You know what you are being paid to do? :)

Duane.

adlo
Posting Yak Master

108 Posts

Posted - 2004-09-03 : 10:33:33

quote:
Originally posted by spirit1

will this do?

select case when SkillsRequiredCount=MySkillsCount then 1 else 0 end as SkillsMatch
from ...

Go with the flow & have fun! Else fight the flow :)

This works, except for when MySkillsCount and SkillsRequiredCount=0 then 0 is returned.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-03 : 10:33:41

well there are day where things are slow and then i can hang around here...
today i think i replied to three posts all together...

so i guess i get some work done too

Go with the flow & have fun! Else fight the flow :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-03 : 10:36:14

quote:

This works, except for when MySkillsCount and SkillsRequiredCount=0 then 0 is returned.

well if both are 0 then they match so the condition is true...
what would you like to do in that case?

Go with the flow & have fun! Else fight the flow :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-03 : 10:37:23

quote:
Originally posted by adlo

quote:
Originally posted by spirit1

will this do?

select case when SkillsRequiredCount=MySkillsCount then 1 else 0 end as SkillsMatch
from ...

Go with the flow & have fun! Else fight the flow :)

This works, except for when MySkillsCount and SkillsRequiredCount=0 then 0 is returned.

So try:

select case when SkillsRequiredCount=MySkillsCount and SkillsRequiredCount>0 then 1 else 0 end as SkillsMatch
from ...

Corey

adlo
Posting Yak Master

108 Posts

Posted - 2004-09-03 : 10:47:04

My mistake, it seems that when both are "null" (and not 0) then they are not equal to each other.

I added
WHEN MandatorySkillCount IS NULL then 1
to the case
and it works

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-03 : 10:49:41

you could also do:

select case when isnull(SkillsRequiredCount,1)=isnull(MySkillsCount,1) then 1 else 0 end as SkillsMatch
from ...

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources