July 15, 2011 at 4:48 am
Does anyone have an elegant solution for this problem? Essentially I want to do the equivalent of an IN on the result of GetDescendantOf.
SELECT
something
FROM
somewhere
WHERE
hierachyid.IsDescendantOf((SELECT hierachyid FROM somewhere_else)) = 1
This actually works as long as the subquery only returns zero or one result. As soon as there are two or more results it falls over.
Thanks in advance!
July 15, 2011 at 5:46 am
I believe that you are refering to the following issue:
SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 15, 2011 at 2:00 pm
villychilly (7/15/2011)
Does anyone have an elegant solution for this problem? Essentially I want to do the equivalent of an IN on the result of GetDescendantOf.SELECT
something
FROM
somewhere
WHERE
hierachyid.IsDescendantOf((SELECT hierachyid FROM somewhere_else)) = 1
This actually works as long as the subquery only returns zero or one result. As soon as there are two or more results it falls over.
Thanks in advance!
I don't have a hierarchyid in place to test so no promises but you could give this technique a shot:
SELECT sw.something
FROM somewhere_else se
CROSS APPLY (
SELECT something
FROM somewhere
WHERE hierachyid.IsDescendantOf(se.hierachyid) = 1
) sw (something)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 15, 2011 at 2:52 pm
Correct me if I'm mistaken but if the subquery does not return a result the you code bombs out?
I hate to mention it but I took a hit on an Oracle Exam quiz on this.
As I recall the inner query must return a record or you bomb out regardless of whether you are using SQL Server,Oracle, DB2, etc.
To avoid this problem you have to replace the NULL with a Zero to avoid the error.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 15, 2011 at 3:02 pm
Welsh Corgi (7/15/2011)
Correct me if I'm mistaken but if the subquery does not return a result the you code bombs out?You have to replace the NULL with a Zero to avoid the error.
Not sure what you mean :ermm:
Can you please elaborate on the case when an error may be generated?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 15, 2011 at 3:58 pm
Hi again, and thank you both very much for your replies. I was obviously having a bit of a bad morning and Welsh Corgi, your reply kickstarted my brain in the right direction. To go back to the pseudo code I put in the OP, the solution was:
SELECT
something
FROM
somewhere
INNER JOIN
somewhere_else
WHERE
hierachyid.IsDescendantOf(somewhere_else.hierarchyid) = 1
Seems so simple in retrospect. The real code involves several other complex factors, so that's my excuse and I'm sticking to it! Thanks again for your replies, much appreciated!
July 15, 2011 at 4:34 pm
opc.three (7/15/2011)
Welsh Corgi (7/15/2011)
Correct me if I'm mistaken but if the subquery does not return a result the you code bombs out?You have to replace the NULL with a Zero to avoid the error.
Not sure what you mean :ermm:
Can you please elaborate on the case when an error may be generated?
If the inner subquery in Oracle returns a Null result you get an inconsistent result. SO you have to replace it with a zero.
I'm not sure about SQL Server although it would not be hard to test. However I'm positive about Oracle.
I could not even find the example on the internet and someone borrowed my textbook.
Edit: Even though the book was only 9i I need to re-purchase the book from Amazon.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 15, 2011 at 5:17 pm
villychilly (7/15/2011)
Hi again, and thank you both very much for your replies. I was obviously having a bit of a bad morning and Welsh Corgi, your reply kickstarted my brain in the right direction. To go back to the pseudo code I put in the OP, the solution was:SELECT
something
FROM
somewhere
INNER JOIN
somewhere_else
WHERE
hierachyid.IsDescendantOf(somewhere_else.hierarchyid) = 1
Seems so simple in retrospect. The real code involves several other complex factors, so that's my excuse and I'm sticking to it! Thanks again for your replies, much appreciated!
Sometimes all it takes is hearing someone else talk about your question for inspiration to strike! Happy you got it sorted villychilly 🙂
Welsh Corgi (7/15/2011)
opc.three (7/15/2011)
Welsh Corgi (7/15/2011)
Correct me if I'm mistaken but if the subquery does not return a result the you code bombs out?You have to replace the NULL with a Zero to avoid the error.
Not sure what you mean :ermm:
Can you please elaborate on the case when an error may be generated?
If the inner subquery in Oracle returns a Null result you get an inconsistent result. SO you have to replace it with a zero.
I'm not sure about SQL Server although it would not be hard to test. However I'm positive about Oracle.
I could not even find the example on the internet and someone borrowed my textbook.
Edit: Even though the book was only 9i I need to re-purchase the book from Amazon.
Oh! I thought you were referring to the CROSS APPLY code I posted. If there is a problem or potential problem there I would like to know...I am still learning new things about APPLY every day.
I write a grand total of 0 lines of code for Oracle these days 🙂 I haven't done anything for Oracle in the last 5 years, and by now I have forgotten what little I picked up cross-training supporting a third-party vendor's database way back when. Even still, I don't worry much whether code I write for SQL Server will run on an Oracle database, or any other RDBMS for that matter. Some folks that frequent these forums are very passionate about the SQL-portability topic, but not me. I will say it's useful to know some of the major platform differences but other than that it's not really important for me when developing T-SQL.
Sorry about your textbook...it sounds like your pursuing learning two of the major vendor platforms. That's admirable! I have my hands full just [trying to] keep up with one 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply