DB2的with

tristan_wang

with temptable(COMCODE,UPPERCOMCODE,Validstatus)  as  (select root.COMCODE,root.UPPERCOMCODE,root.Validstatus from PRPDCOMPANY root where root.COMCODE=''union all select sub.COMCODE,sub.UPPERCOMCODE,sub.Validstatus from PRPDCOMPANY sub,temptable super where sub.UPPERCOMCODE=super.comcode and sub.comcode<>super.UpperComCode and sub.Validstatus = '1')select COMCODE from temptable  ORDER BY ComCode