How can i use Recursive CTE here

Hello Everyone,

I have a purchase order table that holds say 2 columns.PO andOrgPO. That isPurchase Order # andOriginal Purchase Order # respectively. Assume i have the following rows in the table.

PO OrgPO

-- --

po1 NULL

co1 po1

co2 co1

co3 co2

po2 NULL

cpo1 po2

po3 NULL

Now what i would like to report in the output is the PO and along with the lastly generated change order for that po. For eg,

PO LastCO

-- --

po1 co3

po2 cpo1

po3 po3

Currently i 'm using function to achieve this effect and i believe this is not the efficient way. I would like to generate this in a much efficient way. Please help me to achieve this.

Assume your table named 'pot' and here's the code below:

WITH x (newPO, newLastCO, depth)AS(SELECTCASEWHEN OrgPOISNULLTHEN POELSE OrgPOEND AS newPO, POAS newLastCO, 0AS depthFROM potWHERE OrgPOISNULLUNIONALLSELECT x.newPO, POAS newLastCO, x.depth + 1FROM pot pJOIN xON x.newLastCO = p.OrgPO)SELECT x.newPO, x.newLastCOFROM xINNERJOIN (SELECT newPO,MAX(depth)AS deepestFROM XGROUP BY newPO)AS yON x.newPO = y.newPOAND x.depth = y. deepestORDER BY x.newPO

|||

Hi jackyang,

Thanx for the solution man