Archives of the TeradataForum

Message Posted: Wed, 18 Aug 2004 @ 14:38:42 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Matrix/Array query?

From:		Dieter Noeth

Anomy Anom wrote:

From this sample table, I'm trying to write a query that would list one name/item pair for each appearance of a '1' in the database. For example, the above table would give an output like this:

     select name, 'SHIRT'
     from test_01
     where shirt = '1'
     union all
     select name, 'PANTS'
     from test_01
     where pants = '1'
     union all
     select name, 'SOCKS'
     from test_01
     where socks = '1'
     union all
     select name, 'SHOES'
     from test_01
     where shoes = '1'
     union all
     select name, 'HAT'
     from test_01
     where hat = '1'
     union all
     select name, 'COAT'
     from test_01
     where coat = '1';

     create volatile table vt_temp
     (item char(5) not null primary key uppercase)
     on commit preserve rows;

     ins vt_temp ('SHIRT');
     ins vt_temp ('PANTS');
     ins vt_temp ('SOCKS');
     ins vt_temp ('SHOES');
     ins vt_temp ('HAT');
     ins vt_temp ('COAT');

     select name, item
     from test_01, vt_temp
     where item = 'SHIRT' and shirt = '1'
     or item = 'PANTS' and pants = '1'
     or item = 'SOCKS' and socks = '1'
     or item = 'SHOES' and shoes = '1'
     or item = 'HAT' and hat = '1'
     or item = 'COAT' and coat = '1'
     ;

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference