I am facing trouble in writing a recursive SQL query in aws redshift using different columns from two tables , in most of the connect by prior tutorials i have seen the recursion is done on the columns of the same table but i am unsure of how to do this using different tables, this is a sample of what i have come up with
I have written the followin query where col1, col2 , col4 and so on are column names from table test 123 refered as t and somecolumn is the column i have used from another table u on which the connect by has been done
select t.col1,t.col2,t.col3.t.col4 from test 123 as t
start with t.col1='12345'
connect by prior right(concat('000000`, split_part(u.somecolumn,'-',1)),10)=t.col2 and prior right(concat('000000`, split_part(u.somecolumn,'-',2)),6) =t.col4 from test345 u
right, concat and split_part are used for correctly formatting the data due to some data issues in the table. I am not sure if this is the correct syntax or not, can someone please help me out Error SQL Error [42601]: ERROR: syntax error at or near "right"¶ Position: 153
