All Forums Database
14 May 2014
Urgent help required with Recursive query

I have a table to be updated having 3 columns ext_JobGroupCd,ext_JobCd,ext_OrderIngroup
When ever some body called a SP with ext_JobGroupCd,ext_JobCd,new_orderInGroup then the ext_orderInGroup of ext_JobGroupCd,ext_JobCd should be changed to new_orderInGroup and accordingly ext_OrderIngroup of other ext_JobCd s of each ext_JobGroupCd should be altered.
ie : Each ext_JobGroupCd has multiple ext_JobCd and are rated from 1 to n as ext_OrderIngroup
where n is number of ext_JobCd exists in ext_JobGroupCd
When I execute the beløow script I am not able to get result due to some problem can some body help asap
WITH RECURSIVE UPDATE_JOBINGROUP
(
ext_JobGroupCd,
ext_JobCd,
ext_OrderInGroup,
new_OrderInGroup) AS
(
SELECT ext_JobGroupCd,ext_JobCd,ext_OrderInGroup,3 AS new_OrderInGroup FROM D_EdwTmp.ext_SaJobInJobGroup
where ext_ActiveRowInd='Y' and ext_JobGroupCd='TableLoadedTasks' and ext_JobCd='RunFlush'
UNION ALL
SELECT A.ext_JobGroupCd,A.ext_JobCd,A.ext_OrderInGroup,B.new_ OrderInGroup+1FROM UPDATE_JOBINGROUP B
INNER JOIN
D_EdwTmp.ext_SaJobInJobGroup A
ON A.ext_JobGroupCd=B.ext_JobGroupCd
WHERE A.ext_OrderInGroup>3 AND A. ext_ActiveRowInd='Y' and A.ext_JobGroupCd='TableLoadedTasks'
)
SEL ext_JobGroupCd, ext_JobCd, ext_OrderInGroup, new_OrderInGroup FROM UPDATE_JOBINGROUP

You must sign in to leave a comment.