Unique sequence object for each row returned from select statement

[Antanas]

Hi folks. I need unique sequence object generated for each row returned
form select statement. Here is my ADDID() UDF:

CREATE FUNCTION DB2ADMIN.ADDID()
RETURNS CHAR(6)
NOT DETERMINISTIC
F1: BEGIN ATOMIC
DECLARE ID CHAR(6);
SET ID = 'BB' CONCAT (substr(char(cast(NEXT VALUE for
DB2ADMIN.SEQUENCEID as decimal(4, 0))), 1, 4)) ;
RETURN ID;
END
$

How can I make my function ADDID() get executed mutiple times if rows
returned are more than one in this select statement?

SELECT XML2CLOB(
XMLELEMENT (
NAME "Cars",
XMLELEMENT (NAME "CarMake", CAR_MAKE),
XMLELEMENT (NAME "CarID", DB2ADMIN.ADDID())
)
)
FROM AUTOS WHERE CAR_COLOR='Red';
That is if I got 3 red cars I need unique sequence object generated for
each car.
Right now it assigns the same BB0008 for all 3 cars.

<Cars><CarMake>Ford</CarMake><CarID>BB0008</CarID></Cars>

<Cars><CarMake>BMW</CarMake><CarID>BB0008</CarID></Cars>

<Cars><CarMake>Ferrari</CarMake><CarID>BB0008</CarID></Cars>

[Knut Stolze]

Antanas wrote:

Hi folks. I need unique sequence object generated for each row returned
form select statement. Here is my ADDID() UDF:

CREATE FUNCTION DB2ADMIN.ADDID()
RETURNS CHAR(6)
NOT DETERMINISTIC
F1: BEGIN ATOMIC
DECLARE ID CHAR(6);
SET ID = 'BB' CONCAT (substr(char(cast(NEXT VALUE for
DB2ADMIN.SEQUENCEID as decimal(4, 0))), 1, 4)) ;
RETURN ID;
END
$

How can I make my function ADDID() get executed mutiple times if rows
returned are more than one in this select statement?

SELECT XML2CLOB(
XMLELEMENT (
NAME "Cars",
XMLELEMENT (NAME "CarMake", CAR_MAKE),
XMLELEMENT (NAME "CarID", DB2ADMIN.ADDID())
)
)
FROM AUTOS WHERE CAR_COLOR='Red';
That is if I got 3 red cars I need unique sequence object generated for
each car.
Right now it assigns the same BB0008 for all 3 cars.

<Cars><CarMake>Ford</CarMake><CarID>BB0008</CarID></Cars>

<Cars><CarMake>BMW</CarMake><CarID>BB0008</CarID></Cars>

<Cars><CarMake>Ferrari</CarMake><CarID>BB0008</CarID></Cars>

How about this:

SELECT row_number() over (), ...
FROM ...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Antanas]

Knut, nope this is not good for me. I need to increment the sequence,
as each object generated in XML must have unique object id.

[Knut Stolze]

Antanas wrote:

Knut, nope this is not good for me. I need to increment the sequence,
as each object generated in XML must have unique object id.

So you need this to be unique across multiple SELECT statements?

Have a look at the GENERATE_UNIQUE() function. Or use sequences combined
with ROW_NUMBER().

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Antanas]

>So you need this to be unique across multiple SELECT statements?
Yes.

use sequences combined with ROW_NUMBER().

Gute Idee! Knut Dank für einen Tip.

[Antanas]

>So you need this to be unique across multiple SELECT statements?
Yes.

use sequences combined with ROW_NUMBER().

Gute Idee! Knut Dank für einen Tip.