What are the 'Data' and 'value' keywords in SQL Server 2019, and how do they wor-CodePudding

I am working with SQL Server 2019 (standalone server, non-Azure), and I am executing a bath. Following is the batch:

WITH Data (value) AS
(   
SELECT 0   
UNION  ALL
SELECT 1   
)   
SELECT   
   CASE   
      WHEN MIN(value) <= 0 THEN 0   
      WHEN MAX(1/value) >= 100 THEN 1   
   END   
FROM Data ;

I did a lot of research but could not understand what are the Data and value keywords in SQL Server, and how exactly this batch is working. Can someone please help me out?

When I run the batch, I get the following expected error:

Divide by zero error encountered

CodePudding user response：

Data and value are are not "magic" keywords. if you try:

WITH Data (value) AS
(   
SELECT 0   
UNION  ALL
SELECT 1   
)   
SELECT       
   value
FROM Data ;

This returns the two rows with values 0 and 1. 'Data' here is a CTE (common table expressions) and value is the output parameter. The link you mention is merely using the CTE to illustrate that the aggregate MAX(1/value) will be evaluated before the CASE and so will always fail.

This fails in the same way:

WITH MyCTE (param) AS
(   
SELECT 0   
UNION  ALL
SELECT 1   
)   
SELECT   
   CASE   
      WHEN MIN(param) <= 0 THEN 0   
      WHEN MAX(1/param) >= 100 THEN 1   
   END   
   
FROM MyCTE ;

CodePudding user response：

To avoid Divide by zero error use non zero value as divisor. If divisor is zero then convert it to non zero by adding any number as per business.

MAX(1/ CASE WHEN value = 0 THEN 1 ELSE value END)

Also the full code

WITH Data (value) AS
(   
SELECT 0   
UNION  ALL
SELECT 1   
)   
SELECT   
   CASE   
      WHEN MIN(value) <= 0 THEN 0   
      WHEN MAX(1/CASE WHEN value = 0 THEN 1 ELSE value END) >= 100 THEN 1   
   END   
FROM Data ;

CodePudding user response：

Regarding your division by zero, a handy terse way of preventing such an error is to use nullif

WITH Data (value) AS
(   
SELECT 0   
UNION  ALL
SELECT 1   
)   
SELECT   
   CASE   
      WHEN MIN(value) <= 0 THEN 0   
      WHEN MAX(1/NullIf(value,0)) >= 100 THEN 1   
   END   
FROM Data;