Something like this?

select distinct a.ordernumber

from tblorder a

where a.ordernumber not in

(select distinct b.ordernumber

from tblorder b

where right(b.itemnumber,3) 'rtd')

Hi Brett, try putting single quotes around DNPISIMPORT and running this again.

Regards

Phil

Exactly right. Of course, if there might be more than 4 messages, you might have to go dynamic ...

Phil

Hi Karen

I think you'll have to add the Oracle server as a "linked server" - this can be done from Enterprise Manager in SQL Server - and then you'll be...

Something like this should do it:

declare @msg1 as varchar(50), @msg2 as varchar(50), @msg3 as varchar(50), @msg4 as varchar(50)

declare @client as char(6)

set @client = 'a10051'

set @msg1 = (select msg from tblMsg...

If you really have got full design freedom, then here is how I would create the tables:

1) Product (ID (PK), Description, {+ other product info fields})

2) Distributor (ID (PK), Description,...

MAX works fine on string columns.

Joe

I cannot see anywhere in the original post any mention of files.

As for columns and rows not being referred to as fields and records, please explain what practical problems this...

To quote from BOL: "An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then...

Rolf, it would help me to know a bit more about the current db schema. I'm guessing that you have something like:

Product(ID (PK), Description)

DistributorA(ProductID (PK), CurrentPrice)

DistributorB(as above)

DistributorC(as above)

with ProductID...

Is there any reason why you don't have a single table for all the merchants, containing a 'MerchantID' field to identify which merchant it is from? Then you could...

I think something like this should work. You need to read BOL for more info on how triggers work, but this one uses a notional recordset called 'inserted', which...

Use this:

raiserror('',20,1) with log

which will terminate the client connection (severity > 19) and write to the error log.

Regards

Phil

I reckon you need to use the isnumeric() function and treat the numeric and non-numeric codes separately. Try something like this:

SELECT ITEMNO AS Item

FROM [ITEMMAST-10-25-04]

WHERE isnumeric(itemno) = 1 and...

Maybe something like this will help - calculate week number and then group on that - no looping required.

SELECT

(datediff(d,'2001-01-01',entrydate) / 7 + 1) as week

COUNT(orderno) AS NumberofTotalOrders,

...