help with recursive sql

Database

help with recursive sql

Hi, i am a newbie to Terradata and I am runnign this sql but its failing with the below error:

can some one please help..

select s.input_date, s.site_id, element, count(element) from
(WITH RECURSIVE parse_list (input_date, site_id, delim_pos, item_num, element, remainder) AS
(
SELECT
input_date, site_id
,0, 0, CAST('' AS VARCHAR(100) )
,txt
FROM
test
UNION ALL
SELECT input_date, site_id
CASE WHEN POSITION(' ' IN remainder) > 0
THEN POSITION(' ' IN remainder)
ELSE CHARACTER_LENGTH(remainder) END dpos,
item_num + 1,
TRIM(BOTH ' ' FROM SUBSTR(remainder, 0, dpos+1)),
TRIM(SUBSTR(remainder, dpos+1))
FROM parse_list
WHERE dpos > 0

SELECT Failed. 3707:  Syntax error, expected something like a name or a Unicode delimited identifier or a 'SELECT' keyword or '(' or a 'NONTEMPORAL' keyword or 'AS' keyword between '(' and the 'WITH' keyword. 
1 REPLY
Junior Contributor

Re: help with recursive sql

You must define WITH first and then SELECT from it:

WITH RECURSIVE parse_list (input_date, site_id, delim_pos, item_num, element, remainder) AS
(
SELECT
input_date, site_id
,0, 0, CAST('' AS VARCHAR(100) )
,txt
FROM
test
UNION ALL
SELECT input_date, site_id,
CASE WHEN POSITION(' ' IN remainder) > 0
THEN POSITION(' ' IN remainder)
ELSE CHARACTER_LENGTH(remainder) END dpos,
item_num + 1,
TRIM(BOTH ' ' FROM SUBSTR(remainder, 0, dpos+1)),
TRIM(SUBSTR(remainder, dpos+1))
FROM parse_list
WHERE dpos > 0
)
SELECT s.input_date, s.site_id, element, COUNT(element)
FROM parse_list
GROUP BY 1,2,3