oracle - recursive query with union in sql server -

oracle - recursive query with union in sql server -

i want translate oracle query sql server.

but in sql server, "union operator not allowed in recursive part of recursive mutual table look 'temp_tab'." error occured.

how can create it?

>> oracle query

select tree_id, tree_nm, tree_lev_cd, up_tree_id, tree_cd, tree_lrk_ruf_id, sce_xrs_seq_vl ( select tree_id, tree_nm, tree_lev_cd, up_tree_id, tree_cd, tree_lrk_ruf_id, sce_xrs_seq_vl sc_tree_master tree_bj_cd = '02' , usr_id = 'admin' union select a.col_id tree_id, a.col_nm tree_nm, b.tree_lev_cd tree_lev_cd, b.tree_id up_tree_id, b.tree_cd tree_cd, a.scm_id tree_lrk_ruf_id, '999' sce_xrs_seq_vl sc_column a, sc_tree_master b a.scm_id = b.tree_lrk_ruf_id , b.tree_bj_cd = '02' , usr_id = 'admin' ) start up_tree_id = 'tr00000000' connect prior tree_id = up_tree_id order siblings tree_id, sce_xrs_seq_vl

>> sql server query

with temp_tab( tree_id, tree_nm, tree_lev_cd, up_tree_id, tree_cd, tree_lrk_ruf_id, sce_xrs_seq_vl, sort, recursive_level) (select a.tree_id, a.tree_nm, a.tree_lev_cd, a.up_tree_id, a.tree_cd, a.tree_lrk_ruf_id, a.sce_xrs_seq_vl, cast(concat('-',a.tree_id) varchar(100)), 1 recursive_level ( select tree_id, tree_nm, tree_lev_cd, up_tree_id, tree_cd, tree_lrk_ruf_id, sce_xrs_seq_vl sc_tree_master tree_bj_cd = '02' , usr_id = 'admin' union select a.col_id tree_id, a.col_nm tree_nm, b.tree_lev_cd tree_lev_cd, b.tree_id up_tree_id, b.tree_cd tree_cd, a.scm_id tree_lrk_ruf_id, '999' sce_xrs_seq_vl sc_column a, sc_tree_master b a.scm_id = b.tree_lrk_ruf_id , b.tree_bj_cd = '02' , usr_id = 'admin' ) a.up_tree_id = 'tr00000000' union select a.tree_id, a.tree_nm, a.tree_lev_cd, a.up_tree_id, a.tree_cd, a.tree_lrk_ruf_id, a.sce_xrs_seq_vl, cast(concat(b.sort,'/',a.tree_id) varchar(100)), recursive_level + 1 ( select tree_id, tree_nm, tree_lev_cd, up_tree_id, tree_cd, tree_lrk_ruf_id, sce_xrs_seq_vl sc_tree_master tree_bj_cd = '02' , usr_id = 'admin' union select a.col_id tree_id, a.col_nm tree_nm, b.tree_lev_cd tree_lev_cd, b.tree_id up_tree_id, b.tree_cd tree_cd, a.scm_id tree_lrk_ruf_id, '999' sce_xrs_seq_vl sc_column a, sc_tree_master b a.scm_id = b.tree_lrk_ruf_id , b.tree_bj_cd = '02' , usr_id = 'admin' ) a, temp_tab b b.tree_id =a.up_tree_id ) select tree_id, tree_nm, tree_lev_cd, up_tree_id, tree_cd, tree_lrk_ruf_id, sce_xrs_seq_vl temp_tab m order sort

a recursive cte in t-sql mandates utilize of 'union all'. if require behaviour of 'union', utilize keyword 'distinct' in outer query.

sql-server oracle recursion