Keywords not exist - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Keywords not exist

Author

Topic

venkatkrishna
Starting Member

12 Posts

Posted - 2007-05-30 : 05:35:06

Hello,

I could not find the proper solution to convert the stmt to sqlserver 2000.can u help me how to convert the folllwing oracle stmt into sql server2000. here are the keywords used does not support in sqlserver sys_connect_by_path,start with ,connect by prior.

It is in urgent basis.

select f.area_cd,
f.area_desc,
f.region_cd,
f.region_desc,
f.territory_id,
f.region_office_city,
f.region_office_state,
f.territory_city,
f.territory_state,
f.role_cd,
fe.emplid,
fe.preferred_name,
fe.first_name,
fe.last_name,
fe.voice_mail,
fe.internet_address,
am_count,
cec_count,
am_count + cec_count total
from (select t.area_cd,
t.area_desc,
t.region_cd,
t.region_desc,
t.territory_id,
t.region_office_city,
t.region_office_state,
t.territory_city,
t.territory_state,
t.role_cd,
count (case
when h.role_cd = 'AM'
then 1
end) am_count,
count (case
when h.role_cd = 'CEM'
then 1
end) cec_count
from v_da_ff_territory t,
(select cluster_cd,
team_cd,
loc_id,
role_cd,
substr
(sys_connect_by_path (t.territory_id,
','),
2,
instr
(sys_connect_by_path
(t.territory_id,
','
),
',',
1,
2
)
- 2
) rm_territory_id,
case
when level = 2
then territory_id
else substr
(sys_connect_by_path
(t.territory_id,
','
),
instr
(sys_connect_by_path
(t.territory_id,
','
),
',',
1,
2
)
+ 1,
instr
(sys_connect_by_path
(t.territory_id,
','
),
',',
1,
3
)
- instr
(sys_connect_by_path
(t.territory_id,
','
),
',',
1,
2
)
- 1
)
end dm_territory_id,
case
when level = 3
then territory_id
else substr
(sys_connect_by_path
(t.territory_id,
','
),
instr
(sys_connect_by_path
(t.territory_id,
','
),
',',
1,
3
)
+ 1,
instr
(sys_connect_by_path
(t.territory_id,
','
),
',',
1,
4
)
- instr
(sys_connect_by_path
(t.territory_id,
','
),
',',
1,
3
)
- 1
)
end cem_territory_id
from v_da_ff_territory t
where cluster_cd = 'MM'
and t.active_fg = 'Y'
and t.territory_type_cd = 'FL'
--and t.role_cd <> 'TSR'
start with role_cd in ('MMRM', 'TM')
connect by prior territory_id = t.rpt_lvl1_terr) h
where t.cluster_cd = h.cluster_cd
and t.role_cd != 'RC'
and t.territory_id in
(rm_territory_id, dm_territory_id, cem_territory_id)
group by t.area_cd,
t.area_desc,
t.region_cd,
t.region_desc,
t.territory_id,
t.region_office_city,
t.region_office_state,
t.territory_city,
t.territory_state,
t.role_cd) f
left join
(select *
from v_da_field_employee fe1
where fe1.field_active = 'A') fe
on f.territory_id = fe.territory_id
order by area_cd, region_cd

Regards,
Krishna Murthy.K

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-30 : 06:21:03

In v2000 there's no equivalent of the hierachical statements in Orcale. In v2005 they introduced CTEs which enable a similar functionality (and more) but in v2000 you would have to code a loop.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-30 : 06:27:03

http://www.nigelrivett.net/RetrieveTreeHierarchy.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources