Teradata Spool issue in Recursive issue

Database
Enthusiast

Re: Teradata Spool issue in Recursive issue

Thanks a lot Dieter and Raja ,

Actually this data is coming from source system , that's a real time system < Like IPTV data> , so we cant break split is further .

Only the issue i am facing with the duplicate values after concat.

is thier any possible approch to handle this case ?

Meanhwhile i am trying same thing through teradata utilities & unix  ,

means will export all the records(with duplicate ) in a flat file,

then remove duplicate with unix awk 

again load the data into table .

Enthusiast

Re: Teradata Spool issue in Recursive issue

You have not told about the tool that reads real time data. In SAP DS for example, reading from wsdl, you can reformat the data in anyway you want and can load to target.

If you are using Ab Initio, you can read through nested dmls and  reformat in anyway you want.

Again using unix, sed, awk and even normal read line will allow you to extract parts of each line and load again to file, using for loop.

Good luck,

Enthusiast

Re: Teradata Spool issue in Recursive issue

Hi Experts ,

Thanks for your comments.

Finally solution has been implemented  with the fastexport , Unix (duplicate removal) , and again Fastload.

but still for my knowledge  i am looking same kind of solution  through teradata sql.

Enthusiast

Re: Teradata Spool issue in Recursive issue

Hi Abhishek,

Can you please help us with what kind of solution you implemented. I believe we also have a very similar kind of business need. It would be help ful for us to if you extend the way you handled it.

Thanks,

Prashant

Enthusiast

Re: Teradata Spool issue in Recursive issue

Hi Prashant ,

can you please mention your exact requirement here :?

Teradata Employee

Re: Teradata Spool issue in Recursive issue

What exactly do you mean by 'While as per requirement no duplicate allowed in the resiliation column.'? Duplication of values as a chunk or individual values?

Enthusiast

Re: Teradata Spool issue in Recursive issue

--This query would work for maximum 3 values present in Resiliation e.g. 1,2,3

--if it contains more than that then we need to modify the query to accomodate it

--I have seperated column values 1,2,3 into individual rows after removing ','

--We need to insert this result into Temp table and then run recursive query to join

--the rows  to form column again

--To deduplicate the values I have used union.

--If you have INSTR udf installed then solution would become very easy.

--I have used complex SUBSTR to imitate INSTR function

--The following query will give you unique values in Resilence column which was major hurdle.

SELECT * FROM

(

SELECT

Day_id, content_id, Line_id,

SUBSTR(Resiliation,1,INDEX(Resiliation,',')-1) AS COL1

FROM Test

UNION

SELECT

Day_id, content_id, Line_id,

CASE WHEN INDEX(SUBSTR(Resiliation,INDEX(Resiliation,',')+1),',')>0 THEN

SUBSTR(

SUBSTR(Resiliation,INDEX(Resiliation,',')+1),1,

INDEX(SUBSTR(Resiliation,INDEX(Resiliation,',')+1),',')-1)

ELSE

SUBSTR(

SUBSTR(Resiliation,INDEX(Resiliation,',')+1),1

)

END  AS COL1

FROM Test

UNION

SELECT

Day_id, content_id, Line_id,

CASE WHEN INDEX(SUBSTR(Resiliation,INDEX(Resiliation,',')+1),',')>0

THEN

SUBSTR(SUBSTR(Resiliation,INDEX(Resiliation,',')+1),

INDEX(SUBSTR(Resiliation,INDEX(Resiliation,',')+1),',')+1

)

WHEN INDEX(SUBSTR(SUBSTR(Resiliation,INDEX(Resiliation,',')+1),

INDEX(SUBSTR(Resiliation,INDEX(Resiliation,',')+1),',')+1

),',')>0 THEN

SUBSTR(SUBSTR(Resiliation,INDEX(Resiliation,',')+1),

INDEX(SUBSTR(Resiliation,INDEX(Resiliation,',')+1),',')+1,

INDEX(SUBSTR(SUBSTR(Resiliation,INDEX(Resiliation,',')+1),

INDEX(SUBSTR(Resiliation,INDEX(Resiliation,',')+1),',')+1

),',')-1)

ELSE

'NULL'

END  AS COL1

FROM test)T

WHERE col1<>'NULL'

 ORDER BY col1

Let me know if anything is missing.