Tags: addid, database, db2, folks, function, generated, mysql, object, oracle, returned, returnedform, row, select, sequence, sql, statement, udfcreate, unique

Unique sequence object for each row returned from select statement

On Database » DB2

3,647 words with 5 Comments; publish: Tue, 20 May 2008 11:14:00 GMT; (25062.50, « »)

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>

All Comments

Leave a comment...

  • 5 Comments
    • 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

      #1; Tue, 20 May 2008 11:15:00 GMT
    • 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.

      #2; Tue, 20 May 2008 11:16:00 GMT
    • 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

      #3; Tue, 20 May 2008 11:17:00 GMT
    • >So you need this to be unique across multiple SELECT statements?

      Yes.

      >use sequences combined with ROW_NUMBER().

      Gute Idee! Knut Dank f=FCr einen Tip.

      #4; Tue, 20 May 2008 11:18:00 GMT
    • >So you need this to be unique across multiple SELECT statements?

      Yes.

      >use sequences combined with ROW_NUMBER().

      Gute Idee! Knut Dank f=FCr einen Tip.

      #5; Tue, 20 May 2008 11:19:00 GMT