November 2, 2006 at 6:36 am
Hi everyone, I'm trying to create a recursive query for the first time and I keep running into the same error message. I've tried all I can think of. Here is the query:
WITH
ParentInfo (ali_key, parent_key,treelevel,fy,newparent_tree_sec)
AS
(
SELECT ali_key, parent_key, 1 as treelevel,fy, '1!' + CAST(ali_key as varchar(max))+'!' as newparent_tree_sec
FROM administration_fy
where current_hier <> 0
and parent_tree_sec is null
UNION ALL
SELECT a.ali_key, a.parent_key, treelevel + 1 as treelevel,a.fy, newparent_tree_sec + cast(a.ali_key as varchar(20))+ '!'as newparent_tree_sec
FROM administration_fy a
INNER JOIN ParentInfo P on a.parent_key = P.ali_key and a.fy = P.fy
WHERE a.current_hier <>0
AND a.parent_key = P.ali_key
)
SELECT a.ali_key, a.parent_key, treelevel,a.fy, newparent_tree_sec
FROM administration_fy a
INNER JOIN ParentInfo on a.ali_key = P.ali_key and a.fy = P.fy
WHERE a.current_hier <> 0
and a.parent_tree_sec is null
order by newparent_tree_sec
The error message I get it:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "P.ali_key" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "P.fy" could not be bound.
Can you 1) please help with the query. 2) Explain what this error message means.
Thanks!
November 2, 2006 at 12:30 pm
In the last section, you didn't alias ParentInfo as P like you did in the earlier section.
INNER JOIN ParentInfo <insert alias here> on a.ali_key = P.ali_key and a.fy = P.fy
November 3, 2006 at 5:58 am
THANKS! That worked. Also, do you know how to just get the very last level to display? I just need the highest level. I don't want to see level 1, 2, or 4. But I need the data in Level 5 only. Each time it loops, the data is displayed. I just need the final loop with all the data. 🙁
November 3, 2006 at 9:21 am
A couple of ideas for that:
1. Insert the results into a table variable and then select where the level = the max level in the table.
2. Make the whole query into a dynamic table and select where the level = the max level from it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply