ARRAYS in Teradata Studio - comparing fields

Teradata Studio
Highlighted
Fan

ARRAYS in Teradata Studio - comparing fields

Hi,

 

I'm trying to compare columns which could probably done by using a do-loop statement for arrays but not sure if this or how it can be done in Teradata Studio.

 

To illustrate, I have to get the corresponding start_date<num> field of when the pmt_meth<num> field encounters the first "I" value.

record_numpmt_meth1pmt_meth2pmt_meth3pmt_meth4pmt_meth5start_date1start_date2start_date3start_date4start_date5
12345PTInullnull1/01/20181/02/20181/03/2018  

 

I was thinking of transposing them into 1 row each for pmt_meth & start_date but this needs to be done across a table of aounr 500k records and pmt_meth/start_date goes as far as 1-24 sequence each, not just 5 as shown above, so that didn't seem most efficient. 

 

Thank you in advance!


Accepted Solutions
Teradata Employee

Re: ARRAYS in Teradata Studio - comparing fields

A simple case will do the trick :

select case 'I'
         when pmt_meth1 then start_date1
         when pmt_meth2 then start_date2
         when pmt_meth3 then start_date3
         when pmt_meth4 then start_date4
         when pmt_meth5 then start_date5
       end as clc
  from MyTable

You have to remember that once a condition is met, the case command stops further testing.

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: ARRAYS in Teradata Studio - comparing fields

A simple case will do the trick :

select case 'I'
         when pmt_meth1 then start_date1
         when pmt_meth2 then start_date2
         when pmt_meth3 then start_date3
         when pmt_meth4 then start_date4
         when pmt_meth5 then start_date5
       end as clc
  from MyTable

You have to remember that once a condition is met, the case command stops further testing.

Teradata Employee

Re: ARRAYS in Teradata Studio - comparing fields

It's not clear what you are trying to do. You may need to give a more comprehensive example of the source and expected results. Maybe:

 

MIN(CASE WHEN pmt_meth<n> = 'I' THEN start_date<n> END)

Fan

Re: ARRAYS in Teradata Studio - comparing fields

Thank you very much, Waldar. This has been most helpful.