Menu
Why does this not work?
select *
from
(
select membership_number
from members
where membership_number not like '%[^0-9]%'
) mem
where cast(membership_number as int) > 2
See SQL Fiddle Demo.
The subquery should filter out data that is non numeric, and the outer query is casting this to an integer so that I can look for anything > 2.
It seems like it is running the where clause of the outer query first. How do I get around this?
875
Nested Query – In Nested Query, Inner query runs first, and only once. Outer query is executed with result from Inner query. Hence, Inner query is used in execution of Outer query.
SQL executes innermost sub query first, and then the next level. The results of the sub query are the query conditions of the primary query. So in this case, the query sequence is sub query-> primary query, then the option b is the right answer.
A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select. The inner query executes first before its parent query so that the results of an inner query can be passed to the outer query.
SQL's from clause selects and joins your tables and is the first executed part of a query. This means that in queries with joins, the join is the first thing to happen. It's a good practice to limit or pre-aggregate tables before potentially large joins, which can otherwise be very memory intensive.
Maybe that:
select *
from
(
select
membership_number
from
members
where
membership_number not like '%[^0-9]%'
) mem
where Try_Convert(int, membership_number) > 2
Very interesting, I tried to reproduce this on SQL Server and found next. I changed your query to simple just to make sure that query will not fail and I can see the execution plan:
select *
from
(
select membership_number
from members
where membership_number not like '%[^0-9]%'
) mem
where membership_number > '2'
Execution plan is has Table Scan with predicate:
[master].[dbo].[members].[membership_number]>'2'
AND NOT [master].[dbo].[members].[membership_number] like '%[^0-9]%'
So this is because SQL Optimization engine works in this way (as somebody said - nobody can guarantee you the order of where clauses). One of the ways to fix it probably is to use ISNUMERIC before
select *
from
(
select membership_number
from members
where membership_number not like '%[^0-9]%'
) mem
where ISNUMERIC(mem.membership_number) = 1 and cast(mem.membership_number as int) > 2
I had the issue before. What I did was:
1, you can have a view which does:
select membership_number
from members
where membership_number not like '%[^0-9]%'
2, or use temp table for it
3, or use case clause:
select *
from
(
select membership_number
from members
where membership_number not like '%[^0-9]%'
) mem
where (CASE WHEN ISNUMERIC(membership_number) THEN cast(membership_number as int) ELSE 0 END) > 2
did not have a elegant solution, but hope this helps
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
