Andy DBA (8/10/2009)

---

So my question is: Does anyone have strong recommendations (other than readability:-D) regarding LEFT JOIN versus either of the other two? Are there any circumstances in SQL...

Robert,

You can stop an update from occurring by issuing a rollback within the trigger.

In another thread you mentioned that you are using SQL2000, so this code won't work for you, sorry. As this is a SQL2005 forum I assumed you were using...

CTEs and ranking functions are not compatible with SQL2000. You will have to use the derived table approched mentioned earlier.

Create the tables using three part names, then you don't need any USE statements in your procedure.

CREATE TABLE DatabaseName.dbo.TableName ...

I'm going to assume that items are in a separate table to order.

with BuyerOrder as

(

select

b.order_id, b.buyer_id, o.order_desc, i.item_desc,...

Assuming this is SQL2005 the following query will work.

with BuyerOrder as

(

select

b.order_id, b.buyer_id, o.order_desc,

row_number() over (partition by b.buyer_id order by b.order_id) as RowNum

from buyer b

join [order] o

on b.order_id = o.order_id

)

select...

OK, based on the original question posed, your solution is valid.

I guess I got carried away and lost sight of the "requirements" :blush:

Sure, the value 9876504321.1203456 as used in the examples above. Even after changing the code to use DECIMAL(38, 12) the result is 98765043211203461 instead of 98765043211203456.

Unfortunately the STR function takes a float as a...

The error in Jeff's code is easy to fix by setting @Result to FLOOR(@Original) before the loop, however this makes it identical to test 2 except for the fact that...

I believe the following code will do what you want. It should also handle the case where the GroupStartDOW = 7 and GroupEndDOW = 1.

SELECT @RateGroupID = RateDeckGroupID

FROM TelecomRateDeckGroup

WHERE...

I would try using the IBM DB2 UDB for iSeries IBMDASQL OLD DB Provider.

Check out Optimizing Distributed Queries for details of what queries are passed through to the remote server.  Also...