Take a closer look at this:

SELECT      RMSFILENUM,

            rmsbalance

FROM RFINANL a

where rmstrandte = (select max(rmsbalance) from RFINANL)

and

cast(rmstrandte as char(8)) + cast(rmstrantim As...

The expression "AND rf10.rmsbalance <> 0.00" belongs in a WHERE clause, not a HAVING.

Also, since you are storing date and time separately, taking a max of both independantly...

Also you have a non-aggregate expression in your hAVING, are you sure that isn't the cause of the syntax error ?

What are the datatypes of the 2 columns rmstrandte and rmstrantim ?

>>SELECT a.KeyColumn

>>FROM @tablea As a

>>LEFT JOIN (SELECT * FROM @tableb WHERE b.SomeColumn = 'B') b

>>ON a.KeyColumn = b.KeyColumn OR b.KeyColumn IS NULL

That returns all four rows. Which is incorrect.

I'm seeing "IF" statements that are missing a "BEGIN", so that the conditional applies to the next statement only, and not the entire indented block. I think thats' where your...

Thanks for proving my point. In the example below, you get the wrong resultset using a LEFT JOIN and a filter in the WHERE, even if you do phrase it...

Which columns are being updated ?

Are any of the updated columns part of the clustered index ? Are they part of other indexes ?

I have never observed a LEFT JOIN checking for NULL behave any differently than a NOT EXISTS in terms of query plans.

What I have observed are some nasty insidious bugs...

Is the number column unique in Table1 ? Unique in Table2 ?

So, the expression:

 (rf.rmstranamt - rf.rmstranamt10) AS [Current Balance]

... is Current Balance ? And you want to filter on it by comparing it to the top 1 of another sub-query...

Interesting.

I decided to use the 1st 6 characters of a GUID.

Set NoCount On

Declare @Count int

Set @Count = 0

Declare @NextString varchar(6)

Declare @TestTable Table (

  Column1 Varchar(6) Not Null primary key clustered

)

While...

You should probably use SQL Server Profiler and trace at the SQL Server level rather than the operating system to determine what SQL is coming from the app to the...

>>all works tickety boo

Heh, until the tables get large enough and those cursor-like sub selects start running like molasses 🙂

Well, I kinda hoped you would look through the DDL and compare it to your INSERT statement.

eg:

[STATE] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

State is varchar(2)

Look what you're trying to...