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_num pmt_meth1 pmt_meth2 pmt_meth3 pmt_meth4 pmt_meth5 start_date1 start_date2 start_date3 start_date4 start_date5 12345 P T I null null 1/01/2018 1/02/2018 1/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.

Accepted Solutions

## 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 3

## 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.

## 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.