Shulou(Shulou.com)06/01 Report--

When writing this article, I really don't know how to name it, nor do I know how to classify it. This is a case encountered by colleagues, the case is more complex, here to peel off the cocoon, just construct a simple case to show the problem. Let's construct the test data first, as follows:

CREATE TABLE TEST (ID INT, GOOD_TYPE VARCHAR (12), GOOD_WEIGHT NUMERIC (18jing2)) INSERT INTO dbo.TESTVALUES (1,'T1) THEN 1.27) SELECT GOOD_TYPE, CASE WHEN (GOOD_TYPE = 'T1') THEN 99.1 + SUM (GOOD_WEIGHT) ELSE CEILING (SUM (GOOD_WEIGHT)) END AS GrossWeight, SUM (GOOD_WEIGHT) AS NetWeightFROM dbo.TESTGROUP BY GOOD_TYPE

As shown above, why did 99.1 + SUM (GOOD_WEIGHT) become 100? The original SQL is very complex. After we analyzed and excluded various factors, we always missed the point. In all kinds of twists and turns, we found that if we converted in this way (see screenshot below), it would be OK. After a later analysis, it should be the different data types in CASE WHEN that lead to implicit conversion. To be honest, we did not notice the implicit conversion of data types in CASE WHEN before, but why must it be converted from NUMERIC to INT? Instead of implicitly converting INT to NUMERIC, to be honest, if you don't see the official document, if you follow the official document:

When expressions of two different data types are combined with operators, the lower priority data type is first converted to the higher priority data type. If this conversion is not a supported implicit conversion, an error is returned. When combining operators with Operand expressions of the same data type, the result of the operation is that data type

We know that Decimal and NUMERIC are synonyms and can be used interchangeably, and in the official document "data Type priority (Transact-SQL)", Decimal is obviously higher than INT. If you really want to explain in principle, it should be INT conversion NUMERIC (the two data types support implicit conversion), so the more confused you think about it, the more confused you are, but the real Root Cause is not clear yet. And in reports that require high accuracy, this phenomenon will appear as suddenly as Bug. You need to be careful!

Reference:

Https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017

Https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-2017

Summary

The above is the implicit conversion of data types in SQL Server introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply you in time. Thank you very much for your support to the website!

If you think this article is helpful to you, you are welcome to reprint it, please indicate the source, thank you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.