Archives of the TeradataForum

Message Posted: Wed, 14 May 2008 @ 16:47:49 GMT

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

Hi folks,

I'm newbie to teradata, so please forgive me if I ask about something obvious ;)

I have problem with SQL construction, for example, I have table with some data

     -------------- cut --------------
     CREATE SET TABLE example,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
     PROD_ID VARCHAR (60) NOT NULL,
     INSTALLED_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
     PARENT_ID VARCHAR (60) NOT NULL)
     ;

     INSERT INTO example
     VALUES ('01', '2002-11-01', '100');
     INSERT INTO example
     VALUES ('02', '2002-12-01', '100');
     INSERT INTO example
     VALUES ('03', '2003-11-01', '100');
     INSERT INTO example
     VALUES ('04', '2003-12-01', '100');
     INSERT INTO example
     VALUES ('05', '2004-01-01', '100');

     INSERT INTO example
     VALUES ('01', '2002-11-01', '200');
     INSERT INTO example
     VALUES ('02', '2002-12-01', '200');
     INSERT INTO example
     VALUES ('03', '2003-11-01', '200');
     -------------- cut --------------

What I'm trying to achieve is horizontal sequence of two values: PROD_ID and INSTALLED_DATE. I wrote a query to do half of the job:

     -------------- cut --------------

     SELECT
     S1.PARENT_ID
     , S1.PROD_ID
     , MAX(S1.INSTALLED_DATE) OVER (
     PARTITION BY S1.PARENT_ID
     ORDER BY S1.INSTALLED_DATE ASC
     ROWS BETWEEN 0 PRECEDING
     AND 0 PRECEDING
     ) AS DATA0
     , MAX(S1.INSTALLED_DATE) OVER (
     PARTITION BY S1.PARENT_ID
     ORDER BY S1.INSTALLED_DATE ASC
     ROWS BETWEEN 1 PRECEDING
     AND 1 PRECEDING
     ) AS DATA1
     , MAX(S1.INSTALLED_DATE) OVER (
     PARTITION BY S1.PARENT_ID
     ORDER BY S1.INSTALLED_DATE ASC
     ROWS BETWEEN 2 PRECEDING
     AND 2 PRECEDING
     ) AS DATA2
     , MAX(S1.INSTALLED_DATE) OVER (
     PARTITION BY S1.PARENT_ID
     ORDER BY S1.INSTALLED_DATE ASC
     ROWS BETWEEN 3 PRECEDING
     AND 3 PRECEDING
     ) AS DATA3
     , MAX(S1.INSTALLED_DATE) OVER (
     PARTITION BY S1.PARENT_ID
     ORDER BY S1.INSTALLED_DATE ASC
     ROWS BETWEEN 4 PRECEDING
     AND 4 PRECEDING
     ) AS DATA4
     , MAX(S1.INSTALLED_DATE) OVER (
     PARTITION BY S1.PARENT_ID
     ORDER BY S1.INSTALLED_DATE ASC
     ROWS BETWEEN 5 PRECEDING
     AND 5 PRECEDING
     ) AS DATA5
     , COUNT(S1.PROD_ID) OVER (
     PARTITION BY S1.PARENT_ID
     ) AS TCount
     FROM example S1
     QUALIFY
     RANK() OVER (
     PARTITION BY S1.PARENT_ID
     ORDER BY S1.INSTALLED_DATE ASC
     ) = TCount

     -------------- cut --------------

Maybe it is not very elegant, but it works, except that I don't have idea how to add sequence of PROD_ID columns to each DATAX column. The final result would be something like that:

     PARENT_ID|DATA0|PROD_ID0|DATA1|PROD_ID1|...|DATAN|PROD_IDN

I would be grateful for any suggestions.

Best regards.

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