February 27, 2012 at 7:13 am
I need some help please. I have a oraqle query, that i need to change to use in tsql 2005
I am not understanding how to change:
SYS_CONNECT_BY_PATH
BY PRIOR
left join (
select id tnid, actuals,
to_char(substr(SYS_CONNECT_BY_PATH(note, '& '),2)) value_list
from
(
select
tn.prrecordid id,
CASE WHEN REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(4000), tn.prValue), CHAR(13),
''), CHAR(10), ''), CHAR(9), '') IS NULL THEN tn.prCategory ELSE REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(4000), tn.prValue), CHAR(13),
''), CHAR(10), ''), CHAR(9), '') end note,
(te.PRACTSUM/3600) actuals,
COUNT(*) OVER ( partition by tn.prrecordid ) cnt,
ROW_NUMBER () OVER ( partition by tn.prrecordid order by (tn.prtablename)) seq
from prnote tn
left join prtimeentry te on tn.prrecordid = te.prid
where tn.prrecordid is not null
and tn.prtablename = 'PRTimeEntry'
)
where seq = cnt
start with seq = 1
connect by prior seq + 1 = seq
and prior id = id
) tn
on tn.tnid = te.prid
USE TempDB
GO
SET LANGUAGE US_ENGLISH
SET NOCOUNT ON
IF OBJECT_ID('#temp_table') IS NOT NULL
DROP TABLE #temp_table
CREATE TABLE #temp_table
(
id numeric(8)
, PRVALUE ntext
, PRACTSUM float
)
INSERT INTO #temp_table VALUES ('12944243', 'TUESDAY & WEDNESDAY: misc e-mail', '9.0')
INSERT INTO #temp_table VALUES ('12944243', 'MONDAY: GS report + misc e-mail', '9')
INSERT INTO #temp_table VALUES ('12944243', 'THURSDAY: e-mail archiving & handling', '9')
INSERT INTO #temp_table VALUES ('12944243', 'FRIDAY: misc e-mail + GS Reports', '9')
INSERT INTO #temp_table VALUES ('12944244', 'Dealing with host ports access issue', '1.5')
select *
from #temp_table
The inner query provides the table: attached
The results i would like to get back: attached
Thank you for any help provided.
Keri
February 27, 2012 at 9:01 am
There is no direct analogue for this in SQLServer.
You can build recursive queries using CTE, but it will be quite far from using sys_connect_by_path.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply