Posted by Jeff Gilbert on 11/29/05 20:55

I think the key point you made is, "...appearently SQL Server always
> compute this expression". The problem is that the "Number" column can
contain different formats for the various document sub-types. The weird thing
is that the predicate should effectively prevent the MAX() function from
evaluating on anything but the set of values that the THEN clause should
be operating on. But, apparently it doesn't. The reason why inserting the
SUBSTRING(Number, 4, 6) shouldn't work is because for a 5 digit number (anything
but a PO or Work Order) the SUBSTRING() function should return null or empty
string (not sure of the behavior). I really don't get it. I think I have
the semantics as to how the script should work, but apparently the Sql is
getting interpreted incorrectly? Is this a bug?

Jeff...

> Jeff Gilbert (blackhole@gilbertnet.net) writes:
>
>> Ok, I was able to resolve it, but the solution doesn't make sense to
>> me:
>>
>> I basically added to each branch a SUBSTRING(Number, 4, 6) call:
>>
>> CASE @documentType
>> WHEN 1 THEN 'ST-' + dbo.PadNumber(COALESCE(MAX(CAST(SUBSTRING(Number,
>> 4, 6) AS int)), 0) + 1, 6)
>> WHEN 3 THEN dbo.PadNumber(COALESCE(MAX(CAST(SUBSTRING(Number, 4, 6)
>> AS int)), 0) + 1, 8)
>> ELSE dbo.PadNumber(COALESCE(MAX(CAST(SUBSTRING(Number,4,6) AS int)),
>> 0) + 1, 5)
>> END
>> This should fail, but it doesn't; it actually solves the problem. Can
>> anyone shed some light on this behavior?
>>
> I don't see why this would fail, but I realise that I spoke too soon
> in my explanation this morning.
>
> Playing around a little, it appears that the problem lies in the MAX.
> You ask for MAX(CAST Number AS int), and appearently SQL Server always
> compute this expression; probably because it first computes the
> aggregate,
> before it deals with the CASE. If you instead have CAST(MAX(Number) AS
> int),
> you don't get the error message.
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.
> mspx Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

[Back to original message]