Sequence

[khsaunderson]

I am trying to insert a sequence into a table, but want to cycle the sequence dependent upon a specific field.

CREATE SEQUENCE TEMP_SEQ AS DECIMAL(5,0)
START WITH 1  
INCREMENT BY 1  
NO MINVALUE  
NO MAXVALUE  
CYCLE  
CACHE 4;

INSERT INTO Table
(Catalogue char(27),
Sequence decimal(5,0))
SELECT
Catalogue,
NEXTVAL FOR TEMP_SEQ
FROM Catalogue
ORDER BY Catalogue

Using the above example I would want to take the Catalogue, which will appear multiple times, and increment the sequence for each instance of the Catalogue. Then when a new Catalogue is shown in the SELECT query, I would want the sequence to cycle and start back at 1.

Eg,

Catalogue Seq
Cat1 1
Cat1 2
Cat1 3
Cat2 1
Cat2 2
Cat3 1
Cat3 2
Cat3 3
Cat3 4

Any ideas on how I could code this?

Thanks

 

[blom0344]

INSERT CATALOGUE,SEQ INTO SOME_TABLE
SELECT 
CATALOGUE,
ROW_NUMBER() OVER (PARTITION BY CATALOGUE ORDER BY CATALOGUE) as SEQ FROM CATALOGUE

Ties Blom

 

[khsaunderson]

Thanks. For some reason I am getting an error though

SELECT 
CATALOGUE,
ROW_NUMBER() OVER(PARTITION BY CATALOGUE ORDER BY CATALOGUE) AS SEQ
FROM HPINCFL

When I run this I get the following error:

SQL0104 - Token ( was not valid. Valid tokens: , FROM INTO.

Any idea why this might be?

Thanks

 

[MarcLodge]

What's your platform? These functions don't work in every version of DB2, notably they don't generally work on a mainframe.

 

[MarcLodge]

In terms of answering your problem, I would have thought that the best way to achieve this would be by using a trigger on the table.

Marc

 

[khsaunderson]

I'm using DB2 on an AS/400, so I guess that won't work then?

 

[blom0344]

Nope, last time I checked AS400 did not support OLAP style functions. I think Marc is right about the trigger though..

Ties Blom

 

[khsaunderson]

Any help on how I would code the trigger?