testing value of a subquery in the SELECT clause

Question

testing value of a subquery in the SELECT clause

buddy__a

Hall of Fame

Points: 3222
More actions
November 2, 2010 at 12:32 pm

#228707

I have a query that looks like this (this is a simplified version of the query):
select id,
(select * From sometable where keyField = outertable.keyFeld and somefield = somecriteria for xml , path('profile') type) FieldAlias
from outertable
where FieldAlias is not null
for xml auto,elements, root('users')
What I want to do is return no row from the outer table where the subquery in the SELECT clause is null (i.e. returns no data). This is very simplified and the correlated subquery is fairly complex so don't assume that it's easy to reconstruct the query to remove it. The query is designed to produce a hierarchical XML Document of users with their profile (from several tables) as child nodes of the user's root element. So, I tried the above syntax but all I get is a "Invalid column name FieldAlias" error. Is there a way to test this column for null?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

How about moving the correlated subquery into a derived table that you join against (as already specified). If you use an INNER JOIN, if there are no matches then the row will be dropped from the result set.

Edit: ie. move it from the column list that you're selecting to the from clause.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

buddy__a Hall of Fame Points: 3222 More actions · Answer 2

I think the derived table would create an even more complicated query since the query in the derived table would require and additional join to create a key field in addition to the field containing the XML data. It's doable but it would also cause the creation of a temp table with every row from the tables in the derived query as opposed to just selecting the matching rows from the outer table as the correlated query would do. If not otherwise possible, I'll have to restructure it with a derived table. I was just surprised that I couldn't test that field for null.

WayneS (11/2/2010)
How about moving the correlated subquery into a derived table that you join against (as already specified). If you use an INNER JOIN, if there are no matches then the row will be dropped from the result set.
Edit: ie. move it from the column list that you're selecting to the from clause.

WayneS SSC Guru Points: 95460 More actions · Answer 3

buddy__a (11/2/2010)
I think the derived table would create an even more complicated query since the query in the derived table would require and additional join to create a key field in addition to the field containing the XML data. It's doable but it would also cause the creation of a temp table with every row from the tables in the derived query as opposed to just selecting the matching rows from the outer table as the correlated query would do. If not otherwise possible, I'll have to restructure it with a derived table. I was just surprised that I couldn't test that field for null.
WayneS (11/2/2010)
How about moving the correlated subquery into a derived table that you join against (as already specified). If you use an INNER JOIN, if there are no matches then the row will be dropped from the result set.
Edit: ie. move it from the column list that you're selecting to the from clause.

Derived tables are usually much faster than correlated subqueries. With the join condition, it's only running for the records that return records. You might also be able to do this with a cross apply.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

John Rowan SSC Guru Points: 56440 More actions · Answer 4

WayneS (11/2/2010)
buddy__a (11/2/2010)
I think the derived table would create an even more complicated query since the query in the derived table would require and additional join to create a key field in addition to the field containing the XML data. It's doable but it would also cause the creation of a temp table with every row from the tables in the derived query as opposed to just selecting the matching rows from the outer table as the correlated query would do. If not otherwise possible, I'll have to restructure it with a derived table. I was just surprised that I couldn't test that field for null.
WayneS (11/2/2010)
How about moving the correlated subquery into a derived table that you join against (as already specified). If you use an INNER JOIN, if there are no matches then the row will be dropped from the result set.
Edit: ie. move it from the column list that you're selecting to the from clause.
Derived tables are usually much faster than correlated subqueries. With the join condition, it's only running for the records that return records. You might also be able to do this with a cross apply.

Yes, they usually are much faster, but this gets tricky because fo the nested XML elements. I've been forced to write SQL using sub-queries to get this to work right.

buddy__a, can you give us a sample mock up of a table and data that shows your problem? It would be much easier to help with that.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden