|
|
Posted by Erland Sommarskog on 11/29/05 23:57
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)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
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]
|