March 25, 2019 at 1:45 pm
Can anyone explain why, when selecting data into a CTE and then using the results in the CTE to further narrow the data using a field comparison (<>, not equal), the CTE conducts the comparison on all the data in the table instead of only comparing the data used to populate the CTE in the first place?
A little background on my data.
I have the following fields (and data types) with example data:
| Sequence# (int) | Member# (char) | Group# (char) |
| 2300 | AB00010002300 | AB0001 |
| 2301 | 2301 | PP0018 |
The Member# is derived either by a combination of the Group# + Sequence# (with some leading zeros added to the Seq#), or by adding leading leading zeros to the Member# depending on what Group a member is in.
What I was trying to accomplish with a CTE is to select data by Group#, then compare Member# with the Sequence# to identify any that do not match. The obvious issue is converting the CHAR datatype to INT.
When I do a simple SELECT * from dbo.TableName WHERE Group# = 'PP0018', I do in fact only get those members in that group. And it is significant to know that the member numbers for this group are all numeric (no letters).
When I construct my CTE, it is as follows:
WITH Groups AS (
SELECT Sequence#, Member#, Group#
WHERE Group# = 'PP0018'
)
SELECT *
FROM Groups
WHERE Sequence# <> CAST(Member# AS INT)
ORDER BY Sequence#
Again, when I execute the SELECT statement within the CTE, I only get data in Group# PP0018.
HOWEVER, when I execute the entire query, I am getting an error because for some unknown reason it appears the CTE is executing the logic in the WHERE clause on all of the data in the table and not just what I selected in the CTE query. It errors because it can't convert Member#'s that are alphanumeric to integers (which shouldn't be looked at since they were excluded in the CTE).
If I construct this query using a temp table, I have no issues and I get the results I want.
This is quite perplexing.
Thank you for your time!
March 25, 2019 at 2:36 pm
Cast Sequence# as a char and don't convert member# to an int to get around this. At least that's what I would do. There is no FROM in your CTE, example, I'm assuming just a bad cut and paste here since you said when you ran the SQL in the CTE you got what you expected.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
March 25, 2019 at 3:02 pm
Yes, I do have the FROM in the CTE, just didn't get it in here.
I didn't think to try to cast the int as a char. I'll give that a try.
Thanks!
March 26, 2019 at 6:09 am
wegrob - Monday, March 25, 2019 1:45 PMCan anyone explain why, when selecting data into a CTE and then using the results in the CTE to further narrow the data using a field comparison (<>, not equal), the CTE conducts the comparison on all the data in the table instead of only comparing the data used to populate the CTE in the first place?A little background on my data.
I have the following fields (and data types) with example data:
Sequence# (int) Member# (char) Group# (char) 2300 AB00010002300 AB0001 2301 2301 PP0018 The Member# is derived either by a combination of the Group# + Sequence# (with some leading zeros added to the Seq#), or by adding leading leading zeros to the Member# depending on what Group a member is in.
What I was trying to accomplish with a CTE is to select data by Group#, then compare Member# with the Sequence# to identify any that do not match. The obvious issue is converting the CHAR datatype to INT.
When I do a simple SELECT * from dbo.TableName WHERE Group# = 'PP0018', I do in fact only get those members in that group. And it is significant to know that the member numbers for this group are all numeric (no letters).
When I construct my CTE, it is as follows:
WITH Groups AS (
SELECT Sequence#, Member#, Group#
WHERE Group# = 'PP0018'
)SELECT *
FROM Groups
WHERE Sequence# <> CAST(Member# AS INT)
ORDER BY Sequence#Again, when I execute the SELECT statement within the CTE, I only get data in Group# PP0018.
HOWEVER, when I execute the entire query, I am getting an error because for some unknown reason it appears the CTE is executing the logic in the WHERE clause on all of the data in the table and not just what I selected in the CTE query. It errors because it can't convert Member#'s that are alphanumeric to integers (which shouldn't be looked at since they were excluded in the CTE).
If I construct this query using a temp table, I have no issues and I get the results I want.
This is quite perplexing.
Thank you for your time!
There is no guarantee that SQL will process the CTE first. If you need that guarantee, then create a #TempTable, and populate it with the values from the CTE. Then use the #TempTable as your new source.
March 26, 2019 at 6:27 am
That is interesting to know that SQL may not process the CTE first. I did use a temp table instead of the CTE for this reason. Just an interesting CTE 'flaw'.
March 26, 2019 at 7:13 am
wegrob - Tuesday, March 26, 2019 6:27 AMThat is interesting to know that SQL may not process the CTE first. I did use a temp table instead of the CTE for this reason. Just an interesting CTE 'flaw'.
No necessarily a flaw, just a decision that SQL Server makes while processing the query. You might see the same issue if you made the CTE a view and used it in your query.
March 26, 2019 at 7:31 am
We're starting from a fundamental problem in the premise:
Can anyone explain why, when selecting data into a CTE and then using the results in the CTE to further narrow the data using a field comparison
A common table expression is not a table. It's an expression. That's the single most important thing to understand when you start to use CTEs. You're defining an expression, similar to a view or a sub-query. You're not defining a table like with a table variable or temporary table. No temporary storage space is created for the CTE. No data gets moved into the non-existent temporary storage space. It's just a query. It's a unique query because you can define it once and then reuse it multiple times, including in recursion. However, it's just a query that you're adding to the other queries that you're defining. You won't see the behavior that you're asking for because that's not how it works. You can validate this yourself by looking at the execution plans for a CTE and a query using a sub-select or a view. They'll all get defined exactly the same way by the optimizer (assuming the same fundamental code in all three).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 26, 2019 at 10:44 am
Lynn Pettis - Tuesday, March 26, 2019 7:13 AMwegrob - Tuesday, March 26, 2019 6:27 AMThat is interesting to know that SQL may not process the CTE first. I did use a temp table instead of the CTE for this reason. Just an interesting CTE 'flaw'.No necessarily a flaw, just a decision that SQL Server makes while processing the query. You might see the same issue if you made the CTE a view and used it in your query.
Creating a view instead of a CTE was one thing I tried this morning. You are correct! I encountered the same issue. I even thought a materialized view would work. It did not.
March 26, 2019 at 11:12 am
Try it this way:
SELECT *
FROM (
SELECT Sequence#, Member#, Group#
WHERE Group# = 'PP0018'
) AS iq1
WHERE Sequence# <> CASE WHEN Member# NOT LIKE '%[^0-9]%' THEN Member# ELSE -1 END
ORDER BY Sequence#
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 26, 2019 at 12:42 pm
wegrob - Monday, March 25, 2019 3:02 PMYes, I do have the FROM in the CTE, just didn't get it in here.I didn't think to try to cast the int as a char. I'll give that a try.
Thanks!
Did you try this? What was the result?
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
March 27, 2019 at 6:14 am
below86 - Tuesday, March 26, 2019 12:42 PMwegrob - Monday, March 25, 2019 3:02 PMYes, I do have the FROM in the CTE, just didn't get it in here.I didn't think to try to cast the int as a char. I'll give that a try.
Thanks!
Did you try this? What was the result?
I did not. I used a temp table to make the magic happen.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply