How loop through table columns to determine distinct values

Database

How loop through table columns to determine distinct values

SELECT 
  vw_TPDDP.System_NME
  ,vw_TDAD.Date_YYYY_FMT
  ,vw_TDAD.Date_MM_FMT
  ,vw_TPDDP.Company_CDE
  ,SUM(vw_TPF.AMT) Amt
FROM 
  vw_TDAD
   INNER JOIN vw_TPF
   ON (vw_TPF.Date_DIM_ID=vw_TDAD.Date_DIM_ID)
   INNER JOIN vw_TPDDP
   ON (vw_TPF.Profile_DIM_ID=vw_TPDDP.Profile_DIM_ID)
WHERE 
  (  vw_TDAD.Date_YYYY_FMT  =  '2015'
   AND   vw_TDAD.Date_MM_FMT  IN  ( '10','11','12'  ) )
GROUP BY
  1,   2,   3,   4
ORDER BY
  1,   2,   3,   4;

What would the code be for easy way Teradata can loop through each column of a table to determine what each columns distinct values would be.  Is there way to replace one field in the code with the next field from a table to run the code using the field replacement ... looping through the one table.  Example above.

So, if I take the Company_CDE field from the vw_TPDDP table(view), run the surrounding code.  Then come back with the next field in the table replacing vw_TPDDP.Company_CDE with the next field in the table (let's say it is vw_TPDDP.Cancel_CDE), run the surrounding code for it, and so on.  All the way through all the other fields of the table producing a dataset for time code looped through.    How would the code look to do this?

3 REPLIES

Re: How loop through table columns to determine distinct values

It was recommended to me the code around the column which I was looking to be swapped out iteratively should be represented by some type of function or a join with another table, since the complexity increases if I would need to compare the before/after records to one another.  I was told there are windowing functions for something like that (or self joins using row_number()).  All that said I am fairly new at code like this so it would be a challenge for me.  That is why I submitted this post though.

Senior Apprentice

Re: How loop through table columns to determine distinct values

The easiest way is to create the Selects dynamically by joining to dbc.ColumsV top get all column names:

SELECT 
'SELECT
vw_TPDDP.System_NME
,vw_TDAD.Date_YYYY_FMT
,vw_TDAD.Date_MM_FMT
,vw_TPDDP.' || ColumnName || '
,SUM(vw_TPF.AMT) Amt
FROM
vw_TDAD
INNER JOIN vw_TPF
ON (vw_TPF.Date_DIM_ID=vw_TDAD.Date_DIM_ID)
INNER JOIN vw_TPDDP
ON (vw_TPF.Profile_DIM_ID=vw_TPDDP.Profile_DIM_ID)
WHERE
( vw_TDAD.Date_YYYY_FMT = ''2015''
AND vw_TDAD.Date_MM_FMT IN (''10'',''11'',''12'' ))
GROUP BY
1, 2, 3, 4
ORDER BY
1, 2, 3, 4;'
FROM dbc.ColumnsV
WHERE DatabaseName = 'vw_TPDDP'
AND TABLENAME = 'vw_TPDDP'

Caution, all single quotes within the Select must be doubled.

Now cut & paste the result into your query window and run it.

A more dynamic way would be a cursor on dbc.Columns within Stored Procedure, but as you want one result set per column this would fail because a SP is limited to 16 result sets.

Re: How loop through table columns to determine distinct values

Hi Dieter



i am trying to find the outstanding amount for column_Amount like

date      amount

1                  10

2                  20

3                  30

end date     some_amt

for date 1 i need the total amount till end_date including date 1

and for date 2 , i need total amount till end_date including date 2 and so on.

can you plz help me on this

thanks