CASE-question - 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)

CASE-question

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-09-17 : 05:26:56

Are these two queries the same? ->

SELECT SUM(CASE
  WHEN BuyerID = @UserID
    THEN IsNull(Volume, 0)
  WHEN OwnerID = @UserID
    THEN IsNull(Volume * dbo.Currency(OwnerID, @Currency, CreateTime), 0)
  END
  ) AS NewVolume
FROM myTable
WHERE MarketID = @MarketID



SELECT 
  (SELECT IsNull(SUM(Volume), 0)
    FROM myTable
    WHERE BuyerID = @UserID
    AND MarketID = @MarketID
  )
  +
  (SELECT IsNull(SUM(Volume * dbo.Currency(OwnerID, @Currency, CreateTime)), 0)
    FROM myTable
    WHERE OwnerID = @UserID
    AND MarketID = @MarketID
  )

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-17 : 05:54:17

There will be an issue if BuyerID = OwnerID somewhere in the table...

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-17 : 05:56:36

i'd change
IsNull(SUM(Volume), 0)
to
SUM(IsNull(Volume, 0))
in the second query.

if BuyerID is the same as OwnerID somwhere you'll have a problem.
otherwise it's the same.

EDIT

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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-09-17 : 05:57:14

That will never happen. But except for that the two are the same?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-17 : 06:01:46

i'd have to say yes. i tried it on orders in northwind and it returned same data.
but wouldn't it be best if you tested it on your data and see for yourself??

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

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-17 : 06:03:21

They should give the same result provided: check(BuyerID != OwnerID)
Prefer the first one.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-09-17 : 06:15:33

Ok, excellent fellas...I have been using the last one for almost a year now but sql-sills have increased quite drastically in the last few months so I'm trying to make them all better. The plan for the first one looks excellent so I'll definetly use that one. Thanx guys!

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-17 : 07:29:28

quote:
Originally posted by Lumbago

... but sql-sills have increased quite drastically in the last few months so ...

yeah that's what sqlTeam does to you

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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-09-17 : 07:33:18

Umh...now I'm really confused here...I just looked at the query-cost and it actually showed the first one to be more expensive than the second! Query cost relative to the batch was 75% for the first one and 25% for the second one...I don't get it.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-17 : 07:49:26

i think that's because in case he has to compare each value to @userid which is expensive.
in second one you already sum the filtered rows so there is no comparison being made.

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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-09-17 : 07:55:32

What I'm thinking is that the WHERE-statement is done first because of the filtering (naturally) and the where-statement in the first query is alot wider than the ones in the second. I added "AND (BuyerID = @UserID OR OwnerID = @UserID)" to the WHERE-statement in the first query but it didn't make any difference. Guess I'll stick with the one I've been using then

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-17 : 08:01:17

and in the end you get to see that using simple techniques is best.

ironic.... as you wanted to preety up the code

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