All Forums Database
yksn_2001 1 post Joined 09/14
21 Sep 2014
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. 
 

dnoeth 4628 posts Joined 11/04
21 Sep 2014

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

 

Dieter

You must sign in to leave a comment.