Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009
    Posts
    5

    Unanswered: working with stored pprocedures

    Hi All,

    I have a table which has values say
    del_id pt_phase
    1 red
    1 black
    2 red
    2 black

    I need like
    del_id pt_phase
    1 red,black
    2 red,black

    I have written a stored procedure to concatenate the pt_phase.
    But this will work fine if i have the unique del_id.ie I have to traverse through the records of "select distinct(del_id) from table name" each time and insert values.
    In the below code I dont know how to get ditinct(del_id) and work with it.
    If i harcode value eg"1" insead of del_id it is working fine
    Please help!!

    CREATE PROCEDURE CONCAT1()
    LANGUAGE SQL
    BEGIN ATOMIC
    DECLARE s_phase VARCHAR(1000);

    SET s_phase = '';

    FOR v as cur1 CURSOR FOR
    (
    SELECT
    ADMINISTRATOR.TABLE_TRUNC_PRODQUALITY.PQ_TASK_PHAS E_NAME AS PQ_TASK_PHASE_NAME

    FROM
    ADMINISTRATOR.TABLE_TRUNC_PRODQUALITY
    WHERE
    ADMINISTRATOR.TABLE_TRUNC_PRODQUALITY.del_id = del_id
    ORDER BY
    ADMINISTRATOR.TABLE_TRUNC_PRODQUALITY.PQ_TASK_PHAS E_NAME
    )
    DO
    IF s_phase = '' THEN
    SET s_phase = v.PQ_TASK_PHASE_NAME;
    ELSE
    SET s_phase = s_phase || ', ' || v.PQ_TASK_PHASE_NAME;
    END IF;
    END FOR;
    INSERT INTO test_prodquality VALUES (v.del_id,s_phase) ;
    END
    @
    Last edited by db2id; 04-21-09 at 11:40.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    This type of question gets asked a lot. Try using recursion. If you need help, search the forum.

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •