|
|
Posted by Jeff Gilbert on 10/22/58 11:33
Thanks, Erland. I appreciate your help. :)
Jeff...
> Jeff Gilbert (blackhole@gilbertnet.net) writes:
>
>> 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?
>>
> I don't think so. (I will have to admit that I am out on a limb when I
> say this, though.) Consider this repro:
>
> CREATE TABLE [Document] (
> IDDocument uniqueidentifier NOT NULL PRIMARY KEY,
> Doctype tinyint NOT NULL,
> Number varchar(50) NOT NULL,
> )
> go
> CREATE FUNCTION PadNumber (@numberToPad int, @length int)
> RETURNS varchar(50) AS
> BEGIN
> RETURN (REPLACE(STR(@numberToPad,@length), SPACE(1),'0'))
> END
> go
> go
> INSERT Document (IDDocument, Doctype, Number)
> VALUES (newid(), 1, 'ST-00001')
> INSERT Document (IDDocument, Doctype, Number)
> VALUES (newid(), 2, '00001')
> INSERT Document (IDDocument, Doctype, Number)
> VALUES (newid(), 2, '00011')
> INSERT Document (IDDocument, Doctype, Number)
> VALUES (newid(), 3, '00000101')
> go
> SELECT CASE Doctype
> WHEN 1 THEN 'ST-' + dbo.PadNumber(
> COALESCE(MAX(CAST(SUBSTRING(Number, 4, 6) AS int)), 0) + 1,
> 6)
> WHEN 2 THEN dbo.PadNumber(
> COALESCE(MAX(CAST(Number AS int)), 0) + 1, 8)
> WHEN 3 THEN dbo.PadNumber(
> COALESCE(MAX(CAST(Number AS int)), 0) + 1, 5)
> END
> FROM Document
> GROUP BY Doctype
> go
> DROP TABLE Document
> DROP FUNCTION PadNumber
> Here I have replaced the variable by a column. Assume that the CASE
> would
> work as you want it to. Then SQL Server would traverse the table, and
> then for each row compute the MAX value required - which would mean
> yet another iteration over the table.
> Thus, it makes more sense to first compute the aggregated table with
> all the MAX expressions, and then do the column list from there. To
> address this you would have to swap MAX and CAST as I suggested in my
> previous post. Or change the MAX expression to:
>
> MAX(CAST(CASE Doctype WHEN THEN 2 Number END) as int)
>
> 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
>
Navigation:
[Reply to this message]
|