Posted by Erland Sommarskog on 08/26/05 13:46

coosa (coosa76@gmail.com) writes:
> Thanks alot Steve; it worked.
> Could you also please explain "Keys".
> I didn't create a table or attribute called "Keys"; so where does it
> come from? and what does it do?

The Keys is right there in the middle of the query:

select item_id, item_name
from all_view
where not exists (
-- Begin Keys -------------------------
select * from (
select '%'+@search_key1+'%' as like_key
union all select '%'+@search_key2+'%'
union all select '%'+@search_key3+'%'
union all select '%'+@search_key4+'%'
union all select '%'+@search_key5+'%'
) Keys
-- End Keys --------------------------
where not exists (
select * from all_view as V2
where V2.item_id = all_view.item_id
and (
item_value like like_key or item_key like like_key
)
)
)

That's a "derived table". This is sort of a temp table in the middle
of the query. I say sort of, because it is never materialised, and in
fact SQL Server may rearrange the computation order, as long as the
result is preserved. This is a very powerful feature in SQL. (And it's
part of the ANSI standard and should work on most engines.)



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]