IF FUNCTION HELP PLEASE...... - 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

SQL Server Development (2000)

IF FUNCTION HELP PLEASE......

Author

Topic

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-09-30 : 10:36:09

Hello friends,
I have this query in MS Access, do you know how to convert it to SQL Server view?

SELECT empid,
sum(Iff(ISNULL(emp_hours),0,emp_hours)) AS emp_hours
FROM emp
GROUP BY empid

The stupid question is the question you don't ask.
www.single123.com

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-30 : 10:52:35

Use a case statement.


SELECT empid,
       SUM(CASE WHEN emp_hours IS NULL THEN 0 ELSE emp_hours END) AS emp_hours
FROM emp
GROUP BY empid

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-30 : 11:04:58

ISNULL() is much shorter and cleaner than a CASE:

SELECT empid,
sum(ISNULL(emp_hours,0)) AS emp_hours
FROM emp
GROUP BY empid

by the way -- in Access JET, use the NZ() function instead of IIF() as well. much more readable.

- Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-30 : 13:19:15

ok i just can't resist saying this:
use COALESCE not ISNULL.

SELECT empid,
sum(COALESCE(emp_hours,0)) AS emp_hours
FROM emp
GROUP BY empid

COALESCE is ansi, ISNULL isn't.

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

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-30 : 13:50:18

You're just trying to show off since Jenny's Pretty (or So I've read...)

Brett

8-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-30 : 13:57:09

a candidate for a t-shirt

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

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-09-30 : 14:01:44

WOH WOH WOH, it worked.
I tried three ways, they all worked.
I think ISNULL IS USED more often in sql server, but it is ANSI. CASE is a little bit long. COALESCE IS the first time I used and it is ANSI, so I decided to use COALESCE.
Did I make a right decision??????????????????????
Later on, my sql db will be web-based with ColdFusion and ASP....
Any idea???
Thanks.

The stupid question is the question you don't ask.
www.single123.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-30 : 14:09:33

of course they worked. you're on SQLTeam!!!
man i'm in a good mood today...

well isnull is slow as hell. Coalesce is faster.
to get the best results for your situation, check the execution plans of your query.

i just got to ask:
do you make/admin www.single123.com or are you just a member?

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-30 : 14:16:17

spirit --

ISNULL() is slower than COALESCE ()?

how so?

- Jeff

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-09-30 : 14:16:53

I am just a member, i am not that smart to build that site.
come to view my pic on that site, my nname is Kim or Jenny on that site.
thanks.

The stupid question is the question you don't ask.
www.single123.com

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-30 : 14:22:54

quote:
Originally posted by spirit1

of course they worked. you're on SQLTeam!!!

True

quote:

well isnull is slow as hell. Coalesce is faster.
to get the best results for your situation, check the execution plans of your query.

Huh? Can I have a hit....

quote:

i just got to ask:
do you make/admin www.single123.com or are you just a member?

Doesn't this belong in the corral?

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=15

Brett

8-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-30 : 14:25:30

dr.:
you can test it if you have a large table...
maybe that would be a job for Kristen

anyways i once did a test on 500 mil rows where 60% of them had nulls in columns.
i had 6 columns on which IsNull was used.
Coalesce bet IsNull for 15 secs. there where no indexes, nothing on the table. just data

Server wasn't very fast either.

so i have no theoretical backing in this just practical...

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

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-30 : 15:37:21

....yeah...yeah...shop talk...anyone find Jenny's pix?

Brett

8-)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-09-30 : 15:57:17

I think ISNULL and COALESCE work differently wrt how the argument types affect the return type: perhaps that's why you were getting different performance.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-30 : 16:42:30

I would think that in general ISNULL() would be faster, simply because the ISNULL() function always accepts exactly 2 parameters whereas COALESCE can accept multiple parameters.

Of course, i have no idea how internal SQL functions are compiled or executed but I know that in languages like C it would require a few more steps in the function to enumerate through an array of parameters to evaluate them 1-by-1 as opposed to just using a value passed in directly.

- Jeff

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-30 : 18:53:36

Quite an interesting sight... Worldly

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-01 : 06:42:33

dr.: my thinking was exactly the same. i was using just two parameters Coalesce(col1, '') and Isnull(col1, '')
it would be interesting to hear from .... ummm what's his name?? the guy here on SQLTeam that works for MS as SQL group leader or something. i know i saw his posts somewhere.
i'd love to see my results get confirmed or banged to crisps.

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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-10-01 : 08:52:25

chadmat

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-01 : 08:56:30

Arnold has a good point about possible datatype conversion as well.

- Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-01 : 09:27:13

chadmat? really? but i didn't think of him... well i'd love to hear his input on this.

arnold: ok i don't get what you mean... must be because it's friday. how do the arguments affect the return type???
dr.: can you elaborate on that?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources