All Forums Database
mca.anirudh 2 posts Joined 11/14
12 Nov 2014
how to implement connect by level in teradata

Hi Guys
I am new to tera data and we are migrating from oracle to teradata. I have a query like 
SELECT regexp_substr('A,B,C', '[^,]+', 1, level) from dual connect by level <= regexp_count('A,B,C',

',') + 1

 

 

I have searched the forum and it looks like that tera data does'nt support dual and connect by level.How can i migrate it to teradata.

 

Any help will be appreciated!!

 

regards

Anirudh

dnoeth 4628 posts Joined 11/04
12 Nov 2014

Hi Anirudh,
Teradata support Standard SQL's WITH RECURSIVE instead of CONNECT BY.
But you don't need recursion to split a string, in TD14 there's STRTOK_SPLIT_TO_TABLE, e.g.

SELECT * 
FROM TABLE (STRTOK_SPLIT_TO_TABLE(1,'A,B,C',',')
   RETURNS (outkey INTEGER
           ,tokennum INTEGER
           ,token VARCHAR(30) CHARACTER SET UNICODE)
           ) AS dt

 

Dieter

mca.anirudh 2 posts Joined 11/14
13 Nov 2014

Thanks Dieter. It was a great help .
regards
Anirudh

You must sign in to leave a comment.