No problem. Sorry it's not very elegant. If I get the time, I will try to optimise it a bit.

This is ugly code, I haven't tried to make it smarter yet. But I'm just posting it now to show that it is possible:

/*

create table hotels (hotel_id int, theday int)

insert...

The best thing to do is to review your database design, so that you know what the table is called!

You could even use a view if you have a really...

I think it generally goes left-to-right. But it tries to look for conditions that are easier to check first. You should always try to avoid letting it go anywhere near...

If that were true, this would fail:

select *

from sysobjects

where 1/parent_obj = 1

and parent_obj > 0

...because it would evaluate the 1/0 before it had eliminated them.

I'm jumping offline now. Back...

Tough one. I'm not sure myself. I just know that whenever I have a scenario that could cause an error for certain rows if it's calculated in the 'wrong' order,...

No, that's not right. The optimiser works out which way it thinks will be best. That may not be left-to-right at all. But in the example you give, the optimiser...

My point about using the case statement to avoid checking is that:

select case when field = 0 then 0 else 1.0/field end

doesn't try to calculate "1.0/field" if "field=0" is true.

And...

Eek.. I'm wrong.

select *

from sysobjects

where 1/parent_obj = 1

and parent_obj > 0

works just fine. I guess it comes down to the SQL optimiser. It figures that parent_obj > 0 is...

The fact that you can check for zero and still get an error (unless you use a case statement) tells me that it uses the VB style of evaluating everything...

No, sum will give you zero when there are no rows. The problem is that if '0' is in rep.repCPAPrimary, then it gives him a DivZero error.

hehe... I frequently get 'Rod', but rarely 'Ron'. I guess the proximity of 'b' and 'n' on the keyboard helps.

Rob

Sorry - missed a bracket on the end.

... /

SUM(CASE dbo.rep.repCPAPrimary

WHEN 0 THEN 0.000001

WHEN ISNULL(dbo.rep.repCPAPrimary, 0.000001) THEN dbo.rep.repCPAPrimary

ELSE 0.000001 END

*sigh* I wrote a heap, but my browser seems to have lost it. I hit 'post reply', but it didn't go through for some reason.

Anyway... the upshot of it was:

Have...