November 6, 2003 at 3:29 am
Hi All,
Please can someone help me with the following.....I need to convert this Oracle Sript to SQL Server, but I'm at a bit of a loss, especially the recursive bit!
Very Grateful if anyone could help.
Thank You
Here's the Oracle Script:
select distinct inlt.M_UNI_C_LONGNAME, inlt.actor
from
(select mu.M_UNI_C_LONGNAME, f.m_actor_c_name actor, fx.M_ACTL_N_ACTORID,
(select distinct m_actl_n_actorid from obj_m_actorlink x
WHERE M_ACTL_N_ACTORTYPE <> 1 and M_ACTL_N_ACTORTYPE <>32
AND x.M_ACTL_N_ACTORID = fx.M_ACTL_N_ACTORID
start with m_actl_n_actorid = ax.M_ACTL_N_ACTORID
connect by prior x.M_ACTL_N_ID = M_ACTL_N_FATLINKID
) col1
from obj_m_actorlink ax, obj_m_reslink rl, OBJ_M_UNIVERSES MU, obj_m_actor ma, obj_m_actor f, obj_m_actorlink fx
where rl.M_RES_N_ACTLINKID = ax.M_ACTL_N_ID
and rl.M_RES_N_RESID = mu.M_UNI_N_ID
and rl.M_RES_N_RESTYPE = 2
and rl.M_RES_N_STATUS = 1
and ma.M_ACTOR_N_ID = ax.M_ACTL_N_ACTORID
and f.M_ACTOR_N_ID = fx.M_ACTL_N_ACTORID) inlt
where inlt.col1 is not null
\/ / /\/ /\/ `/
\/ / /\/ /\/ `/
November 6, 2003 at 4:15 am
There is currently no equivalent of this in sql server - you have to coed it yourself.
You can use a recursive SP or function but probably better to use a loop.
Cursors never.
DTS - only when needed and never to control.
Cursors never.
DTS - only when needed and never to control.
November 6, 2003 at 4:33 am
What is a recursive SP.....and how might I use a LOOP?
\/ / /\/ /\/ `/
\/ / /\/ /\/ `/
November 6, 2003 at 4:37 am
<*big grin*>
Goostel's ASCII art remind me of some lunch break fun.
http://www.hsk.dk/note/kuriosa/cow-complete.html
Enjoy your meal!
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 6, 2003 at 4:46 am
...but to answer the question, as an example you might want to take a look at
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=19663 on recursive stored procedures
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 6, 2003 at 7:53 pm
Hi there
the "connect by prior" or "tree walk" as I like to call it, has no direct equivalent in SQL Server, there are some interesting changes in yukon that may help. Give google groups search a go, there are 10+ posts with solutions on this one 🙂
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply