@MYDBA

Yes I want to port this code to other platform.

My question is how to solve it, Please don't divert the issue. I have to use the non cte code in different places.

Thank You guys for your reply,

This is the detailed query

;with cte1(id) AS

{

select MyId from table1 T1

inner join table2 T2

on T2.MyId=T1.MyId

Union ALL

select MyId from table1 T1

Join Hierarchy_Table HT

on T1.MyId =...