All Forums Analytics
Karthikeyan_dsk 5 posts Joined 11/13
20 Jul 2016
Replacing duplicates while transpose in Teradata

Hi,
I have two tables as below.
TBL_RULES

TBL_NM

VLD_ID

COL_NM

TABLE1

1000

COL_1

TABLE1

1001

COL_2

TABLE1

1002

COL_2

TABLE1

1003

COL_3

TABLE_LOG

TBL_NM

VLD_ID

REC_NBR

SUCCESS_FLAG

TABLE1

1000

1

N

TABLE1

1001

1

N

TABLE1

1002

1

N

Success flag will be updated based on certain validation rules. Intention is to create a column to find which columns from a table for a row is failing the validations. I am using below query.

select LG.REC_NBR, 
   RTRIM(XMLAGG(TRIM(VR.COL_NM) || ': ' ||TRIM(LG.SUCCESS_FLAG) || ','
                ORDER BY LG.VALIDATION_ID 
               ) (VARCHAR(10000)),', ') AS VLD_DSC
from TBL_RULES  VR
left   join TABLE_LOG LG on LG.TBL_NM = VR.TBL_NM and  LG.VLD_ID= VR.VLD_ID GROUP BY 1

The result is 

REC_NBR

VLD_DSC

1

COL1: N, COL2: N, COL2: N

But I want COL2 status only once. How can i modify my query. The intended result is 

REC_NBR

VLD_DSC

1

COL1: N, COL2: N

 

Karthikeyan_dsk 5 posts Joined 11/13
20 Jul 2016

Making Tables Proper

TBL_RULES		
TBL_NM	VLD_ID	COL_NM
TABLE1	1000	COL_1
TABLE1	1001	COL_2
TABLE1	1002	COL_2
TABLE1	1003	COL_3
TABLE_LOG			
TBL_NM	VLD_ID	REC_NBR	SUCCESS_FLAG
TABLE1	1000	1	N
TABLE1	1001	1	N
TABLE1	1002	1	N

Current Result

REC_NBR	VLD_DSC
1	COL1: N, COL2: N, COL2: N

Intended

REC_NBR	VLD_DSC
1	COL1: N, COL2: N

 

You must sign in to leave a comment.