To be honest, when devs say that a dynamic solution is the 'solution for all their problems' I literally get chills down my spine.

Before you let them play with...

It is the same (only written out more complete) that John suggested in the 2nd post in this thread.

The select between the parens forms a resultset which you can view...

Works for me as well...

Hey, I got the same #posts as you Frank.. (zeroes doesn't count, do they..?)

/Kenneth 

Try this...

SELECT  x.serialno,

        x.maxDate,

        t.countvalue

FROM  (

        SELECT   serialno,

                 max(convert(char(10), date1, 112)) as maxDate 

        FROM     test1

        WHERE    countvalue <> 0

        GROUP BY serialno

      ) x

JOIN    test1...

Another solution is to use a led pipe and apply it to the devs fingers.

If they can't begin being consistent with names even, how can you then trust the stuff...

That was in my mind as well..

If this is the only proc that references this view, and if it's likely that in the forseeable future no other proc or code...

I agree as well.

In the academic world everything can be made perfect, though in the real world everything ultimately has to be a tradeoff.. because it always depends

It's one of those things with these toasters.. They do exactly what you tell them to, not necessarily what you want them to though...

Well... re-reading the original post (last paragraph) I realize that I'm not entirely sure either what the real question is about....

I'm probably lost as well..

Why don't you just leave the dbname out?

There's no point, and not preferred either, to reference tables with dbname.owner.objectname if it's not intended to be a crossdatabase query in the...

Though, you'd still be missing the nice red colour

On the other hand, it's just as 'easy' to replace the double quotes with single

Personally, I do prefer (and use) the 'single-update' method to increment and return the next countervalue in one go. It makes it unnecessary to wrap in explicit transactions and...

Exactly how are you creating the table 'on the fly' ...? It might help to understand what really happened (and also help to avoid it next time)

The reason is that you're using dynamic SQL, which you really should avoid.

Even if you'd get it to work, this code won't behave as you might expect. There is a...

Why do you need it to be dynamic? What's the reason for using the function this way? There may be some other way to solve the underlying actual problem?

/Kenneth