Snowflake, Recursive CTE , Getting error String 'AAAA_50>BBBB_47>CCCC

I am creating a recursive CTE in snowflake for getting complete path an getting following error:

String 'AAAA_50>BBBB_47>CCCC_92' is too long and would be truncated in 'CONCAT'

My script is as follows: (it works fine for 2 levels, starts failing for 3rd level)

    with recursive plant 
    (child_col,parent_col,val )
    as 
    (
    select  child_col, '' parent_col , trim(child_col)  from My_view 
    where condition1 = 'AAA' 
    union all
    select  A.child_col,A.parent_col,                 
    concat(trim(A.child_col),'>')||trim(val)
    from My_view A 
    JOIN plant as B ON trim(B.child_col) = trim(A.parent_col) 
      )
      select distinct * from plant

CodePudding user response：

Most likely the child_col data type is defined as VARCHAR (N), this type is being passed on. Because CONCAT Returns:

The data type of the returned value is the same as the data type of the input value(s).

Try to explicitly cast a type to a string like this cast(trim(child_col) as string):

Full code:

with recursive plant (child_col,parent_col,val )
as (
select  child_col, '' parent_col , cast(trim(child_col) as string)
  from My_view 
 where condition1 = 'AAA' 
 union all
select  A.child_col, A.parent_col, concat(trim(A.child_col),'>')||trim(val)
  from My_view A 
  join plant as B ON trim(B.child_col) = trim(A.parent_col) 
)
select distinct * from plant

Remember that recursion in Snowflake is limited to 100 loops by default. If you want to increase them, you need to contact support.

Reference: CONCAT Troubleshooting a Recursive CTE