Looping in teradata

Database
Enthusiast

Looping in teradata

Hi Guys,

I need some info on looping in Teradata with below requirement. Anny help will be appriciated. Thanks in advance,

source field is string which is having below format,

[{"createdBy":null,"couponNo":1234,"couponAmount":1345,"updatedBy":mike},{"couponNo":7654,"couponAmount":67654,"updatedBy":sam},{"ItemNo":8765,"createdBy":null,"couponNo":7654,"couponAmount":9878,"updatedBy":john}]

We have to split above field as below,

row no       Field1                         Field2                

1             "couponNo":1234       "couponAmount":1345

2             "couponNo":7654       "couponAmount":67654

3             "couponNo":7654       "couponAmount":9878

2 REPLIES
Junior Contributor

Re: Looping in teradata

Hi Mikhil,

what's your TD release?

In TD14:

SELECT
NVP(token, '"couponNo"', ',', ':')
,NVP(token, '"couponAmount"', ',', ':')
FROM
TABLE(REGEXP_SPLIT_TO_TABLE(1,'[{"createdBy":null,"couponNo":1234,"couponAmount":1345,"updatedBy":mike},{"couponNo":7654,"couponAmount":67654,"updatedBy":sam},{"ItemNo":8765,"createdBy": null,"couponNo":7654,"couponAmount":9878,"updatedBy": john}]'
,'},{', 'i')
RETURNS (res INTEGER, tokennum INTEGER, token VARCHAR(250) CHARACTER SET UNICODE)) t1;
) AS dt

Btw, this seems to be JSON format, which is natively supported in TD15.

Enthusiast

Re: Looping in teradata

Thanks' Diter :)

We are using TD 13.0

I used With Recursive & it worked :)