CREATE FUNCTION DEL_SORT(SORTCD VARCHAR(50))
RETURNS table(S1 VARCHAR(50))
NO EXTERNAL ACTION
F1: BEGIN ATOMIC
RETURN
with temptab(SORT_CD,SORT_NAME,PARENT_SORT_CD) as (
select SORT_CD,SORT_NAME,PARENT_SORT_CD
from CXMART.TN_BM_COL_SORT
where SORT_CD=SORTCD
union all
select tn.SORT_CD, tn.SORT_NAME,temp.PARENT_SORT_CD
from CXMART.TN_BM_COL_SORT tn , temptab temp
where tn.PARENT_SORT_CD = temp.SORT_CD
) select SORT_CD from temptab;
END;
select S1 from table(DEL_SORT('Pff64fDD')) as SORT;
DELETE FROM CXMART.TN_BM_COL_SORT WHERE SORT_CD IN( select S1 from table(DEL_SORT('Pff64fDD')) as SORT);