CTE returning error

Question

I wrote a CTE to remove non numeric values from a data set, then get a count of numeric values within a range.

WITH dtr AS ( SELECT resultlevel r FROM dbo.Result WHERE DrugID = 'AMP' AND ISNUMERIC(ResultLevel) = 1 AND AuditStamp > '1/1/2016' AND DeleteFlag = 0 ) SELECT COUNT(*) FROM dtr WHERE CONVERT(INT, r) BETWEEN 50 AND 75

This returns an error in SMS

Msg 245, Level 16, State 1, Line 2 Conversion failed when converting the varchar value 'PND ' to data type int.

This error is completely possible without the 'dtr' query in the CTE.

When I rewrite this, instead of a CTR, but a TEMP table, it works.

SELECT resultlevel r INTO #d FROM dbo.Result WHERE DrugID = 'AMP' AND ISNUMERIC(ResultLevel) = 1 AND AuditStamp > '1/1/2016' AND DeleteFlag = 0 SELECT COUNT(*) FROM #d WHERE CONVERT(INT, r) BETWEEN 50 AND 75

So my questions is why?? I have always thought a CTE was like creating a TEMP table.

TEST DATA

if object_id('tempdb..#temp') is not null drop table #temp create table #temp (result char(5)) insert into #temp (result) values ('1'),('A'),('>2'),('PEN ') ,('@3'),('-2'),('-33') ;with isnum AS ( SELECT result FROM #temp WHERE ISNUMERIC(result) = 1) --Selecting from the CTE yields 1, -2, and -33 all of which can be converted to INT --Running the query with the where clause causes the conversion error SELECT result, ISNUMERIC(result) FROM isnum --WHERE CONVERT(INT,result) > 1

Not exactly - a CTE isn't a temp table. A more accurate description is that it is a temporary view. And will behave as such. — Siyual
– Siyual, Commented Dec 12, 2016 at 21:15
@DMason - receive the same result, but I updated the date so they are the same. — larryr
– larryr, Commented Dec 12, 2016 at 21:23
You should get the same error in the temp if the data is the same.... and select ISNUMERIC('PND ') doesn't equal 1 so I feel like there has to be more here... is there some code you are leaving out? That value shouldn't be int he CTE result set. — S3S
– S3S, Commented Dec 12, 2016 at 21:27
If you run Select isnumeric('PND ') as result; what is the value of result? — xQbert
– xQbert, Commented Dec 12, 2016 at 21:29

gotqn · Accepted Answer · 2016-12-13 08:11:33Z

In SQL Server there is Logical Processing Order of the SELECT statement, which determines when the objects defined in one step are made available to the clauses in subsequent steps:

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

This is how your query is going to be proccesed and your query looks perfectly fine. But sometimes, the SQL Server decides not to follow this order in order to optimize your query.

In your case, the SQL Server might be simplyfing/transforming your query into another and performing the convert function, before applying the where isnumeric filtering.

If we made your query a little more complex (but still giving the same results), the SQL Server is executing the code correctly this time:

;with isnum AS ( SELECT result FROM #temp WHERE ISNUMERIC(result) = 1 GROUP BY result HAVING MAX(result) = result ) SELECT result, ISNUMERIC(result) FROM isnum WHERE CONVERT(INT,result) > 1;

In your case (and this is what I am doing in such situations when different types are stored in one column), you can simply use TRY_CONVERT function:

;with isnum AS ( SELECT result FROM #temp WHERE ISNUMERIC(result) = 1) SELECT result, ISNUMERIC(result) FROM isnum WHERE TRY_CONVERT(INT, result) > 1

Collectives™ on Stack Overflow

CTE returning error

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related