All Forums Database
mnagara 6 posts Joined 11/14
10 Dec 2014
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
 
 

Thanks, Mikhil
dnoeth 4628 posts Joined 11/04
11 Dec 2014

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.

Dieter

mnagara 6 posts Joined 11/14
14 Dec 2014

Thanks' Diter :)
We are using TD 13.0
I used With Recursive & it worked :)

Thanks,
Mikhil

You must sign in to leave a comment.