All Forums Database
Laxchman 2 posts Joined 02/16
17 Feb 2016
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?
 

Laxchman 2 posts Joined 02/16
17 Feb 2016

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.

dnoeth 4628 posts Joined 11/04
18 Feb 2016

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.

Dieter

sriteradata88 3 posts Joined 02/16
18 Feb 2016

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

You must sign in to leave a comment.