Recursive CTE query

1

I am having a problem with SSMS 2017 recognizing a simple CTE recursive query I have created.
When I run this very simple one column query as a SELECT statement I get no problems:

I have changed the info in X to protect the source as this for my real-world job. S/B understood.
SELECT fmHierarchyXXXX.HID

FROM XXXXXXXdbo.fmHierarchyAnalysis; The field is a PK, int, not null.

when I insert the CTE condition on this very same query as shown below:

with HIDcte as (
SELECT fmHierarchyAnalysis.HID
)
select * FROM XXXXXX.dbo.fmHierarchyAnalysis

SSMS throws the following msg:

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "fmHierarchyAnalysis.HID" could not be bound.

The table does exist. This is just beginning of what I hope will a full recursive query on two tables. I used this one since it has less fields.

I suspect this is a syntax error.

2

Change

with HIDcte as (
SELECT fmHierarchyAnalysis.HID
)
select * FROM XXXXXX.dbo.fmHierarchyAnalysis

into

with HIDcte as (
SELECT fmHierarchyAnalysis.HID
FROM XXXXXX.dbo.fmHierarchyAnalysis
)
3

now it throws an incorrect syntax error near the table name. thx anyway

4

The query you provided is not valid - and does not appear to be complete. The CTE portion does not contain a FROM and the actual select does not reference the CTE.

The general form would be:

with myCTE
as (
select ... from sometable
union all
select ... from myCTE
)
select ... from myCTE

We would need the full statement to be able to determine where you are having an issue.

6

Thx Jeff I got it working now. Your help was useful and not condescending. I appreciate that. One or two tweaks did the trick. Now I have found a max 100 recursion will terminated the statement. I need to set a loop on this. Again thx. I won't post the revised query now.