how to change oracle query for 2005 tsql use?

• Keri Taylor

  SSC Enthusiast

  Points: 115

  More actions

  February 27, 2012 at 7:13 am

  #391659

  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

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  February 27, 2012 at 9:01 am

  #1452130

  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.

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]