Hierarchical query running in 10g returns no results in 11g
The data in the two versions is identical.
SELECT rc.RptCategoryID
,rc.Name
,rc.ParentCategoryID ParentCategoryID
,rct.RptCategoryTypeID categorytypeid
,LEVEL navlevel
FROM RptCategory rc
JOIN RptCategoryType rct ON rc.RptCategoryTypeID = rct.RptCategoryTypeID
WHERE rc.active=1
START WITH rc.parentcategoryid = 0
CONNECT BY prior rc.rptcategoryid = rc.parentcategoryid;
10.2.0.3.0 Returns the expected 8 rows
11.2.0.1.0 Returns 0 rows
If I take out the start with clause both versions return 13 rows.
If I rewrite the 11g version with a recursive subquery, it returns the right number of rows, but having two code versions is not a good thing for me.
what am i missing?
SELECT rc.RptCategoryID
,rc.Name
,rc.ParentCategoryID ParentCategoryID
,rct.RptCategoryTypeID categorytypeid
,LEVEL navlevel
FROM RptCategory rc
JOIN RptCategoryType rct ON rc.RptCategoryTypeID = rct.RptCategoryTypeID
WHERE rc.active=1
START WITH rc.parentcategoryid = 0
CONNECT BY prior rc.rptcategoryid = rc.parentcategoryid;
10.2.0.3.0 Returns the expected 8 rows
11.2.0.1.0 Returns 0 rows
If I take out the start with clause both versions return 13 rows.
If I rewrite the 11g version with a recursive subquery, it returns the right number of rows, but having two code versions is not a good thing for me.
what am i missing?
0