I have a nested SQL select statement where the sub select relies on a value from the parent select. However, the query fails due to a null value (I think). 
How do I get the query to ignore the sub select statement each time it comes across a null value?

Here's my query:

```
SELECT ID, Hierarchy, Name, Hierarchy.GetLevel() AS Level, Hierarchy.GetAncestor(1) AS ParentHierarchy,
        (SELECT ID
         FROM  SpecProducts
         WHERE (Hierarchy = ParentHierarchy)) AS ParentHierarchy

FROM          SpecProducts AS SpecProducts_1
WHERE        (EnableDisable IS NULL)
ORDER BY Hierarchy

```

Here is my error message:

Invalid column name 'ParentHierarchy'

\*\**\**\*\**\**\*\**\**\*\**\**\*\**\**\*\**\**\*\* EDIT: 24/04/2012 - 14:50 ***\**\*\**\**\*\**\**\*\**\**\*\**\**\*\**\**\***

Thanks for pointing out the error. Unfortunately I still get the same problem.

Here's the updated query:

```
SELECT ID, Hierarchy, Name, Hierarchy.GetLevel() AS Level, Hierarchy.GetAncestor(1) AS ParentHierarchy,
                         (SELECT  ID
                          FROM    SpecProducts
                          WHERE  (Hierarchy = ParentHierarchy)) AS ParentID
FROM    SpecProducts AS SpecProducts_1
WHERE   (EnableDisable IS NULL)
ORDER BY Hierarchy

```

Error Message:
Invalid Column Name 'ParentHierarchy'

Is the problem because the ParentHierarchy value can be NULL?

\*\* EDIT ***\**\*\**\**\*\**\**\*\**\**\*\***

Ok this works:

```
SELECT   ID, Hierarchy, Name, Hierarchy.GetLevel() AS Level, Hierarchy.GetAncestor(1) AS ParentHierarchy,
         (SELECT   ID AS IDd
          FROM     SpecProducts
          WHERE    (Hierarchy = SpecProducts_1.Hierarchy.GetAncestor(1))) AS ParentID
FROM            SpecProducts AS SpecProducts_1
WHERE        (EnableDisable IS NULL)
ORDER BY Hierarchy

```