Results 1 to 5 of 5

Thread: Query Help

  1. #1
    Join Date
    Dec 2003
    Location
    Philadelphia
    Posts
    6

    Exclamation Unanswered: Query Help

    How would I go about forming a query where I want to put a bunch of Rows together on one primary key.

    Ex. SELECT * FROM DB WHERE PolicyNumber='*****' AND (Type='AA' or Type='BB' or Type='CC')

    Instead of having all of the OR Statements (which leaves multiple rows with the same policy number) I want to put the ORs together into one Cell of the table and comma deliminate them based on PolicyNumber being the Primary Key.

    So how do I write a query that would append data to the type column if there already is a row with the same info in the other columns?

    Thanks in advance

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Query Help

    You can create a function where you would pass the policy number that can be used as a selected column. Within in the function would be a select of the type column for that policynumber and a loop to concatenate the row results. The return from the function would be the concatenated values.

    ie. SELECT policynumber, typeconcat(policynumber) FROM db WHERE.....

    Originally posted by Solius
    How would I go about forming a query where I want to put a bunch of Rows together on one primary key.

    Ex. SELECT * FROM DB WHERE PolicyNumber='*****' AND (Type='AA' or Type='BB' or Type='CC')

    Instead of having all of the OR Statements (which leaves multiple rows with the same policy number) I want to put the ORs together into one Cell of the table and comma deliminate them based on PolicyNumber being the Primary Key.

    So how do I write a query that would append data to the type column if there already is a row with the same info in the other columns?

    Thanks in advance

  3. #3
    Join Date
    Dec 2003
    Location
    Philadelphia
    Posts
    6

    Re: Query Help

    I'm a newb and could use a little more instruction on the function building aspect. Thanks.

    Originally posted by dmmac
    You can create a function where you would pass the policy number that can be used as a selected column. Within in the function would be a select of the type column for that policynumber and a loop to concatenate the row results. The return from the function would be the concatenated values.

    ie. SELECT policynumber, typeconcat(policynumber) FROM db WHERE.....

  4. #4
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Query Help

    Here is a template:

    CREATE FUNCTION typeconcat(pi_policynumber integer)
    LANGUAGE SQL
    NOT DETERMINISTIC
    NO EXTERNAL ACTION
    READS SQL DATA
    RETURNS VARCHAR(254)
    BEGIN ATOMIC
    DECLARE v_message VARCHAR(254) default ' ';
    DECLARE v_length INTEGER;

    FOR c1 AS
    SELECT type FROM x
    WHERE policynumber = pi_policynumber
    DO
    set v_length = length(v_message);
    IF (v_length <= 222 ) THEN

    SET v_message = v_message ||rtrim(type) ||', ';

    END IF;

    END FOR;
    RETURN LTRIM(SUBSTR(v_message, 1, LENGTH(v_message) - 2));
    END

    Also you can visit IBM DB2 web site for manuals, tips, etc.
    http://www-3.ibm.com/cgi-bin/db2www/...dex.d2w/report


    Originally posted by Solius
    I'm a newb and could use a little more instruction on the function building aspect. Thanks.

  5. #5
    Join Date
    Dec 2003
    Location
    Philadelphia
    Posts
    6

    Re: Query Help

    Awesome, Thanks a lot for the help

    Originally posted by dmmac
    Here is a template:

    CREATE FUNCTION typeconcat(pi_policynumber integer)
    LANGUAGE SQL
    NOT DETERMINISTIC
    NO EXTERNAL ACTION
    READS SQL DATA
    RETURNS VARCHAR(254)
    BEGIN ATOMIC
    DECLARE v_message VARCHAR(254) default ' ';
    DECLARE v_length INTEGER;

    FOR c1 AS
    SELECT type FROM x
    WHERE policynumber = pi_policynumber
    DO
    set v_length = length(v_message);
    IF (v_length <= 222 ) THEN

    SET v_message = v_message ||rtrim(type) ||', ';

    END IF;

    END FOR;
    RETURN LTRIM(SUBSTR(v_message, 1, LENGTH(v_message) - 2));
    END

    Also you can visit IBM DB2 web site for manuals, tips, etc.
    http://www-3.ibm.com/cgi-bin/db2www/...dex.d2w/report

Posting Permissions

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