Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2011
    Posts
    4

    Unanswered: Supress Db2 Warning message and to get result in Unix

    I am use these recursive stmt, but it is given warning message and don't shows any output in unix(putty).

    CREATE TABLE rec
    (
    snum INTEGER, -- sentence number
    wordnum INTEGER, -- word number in the sentence
    word VARCHAR(100) -- word being saved
    );

    INSERT INTO rec
    VALUES (1, 1, 'This'), (1, 2, 'is'), (1, 3, 'a'), (1, 4, 'fine'), (1, 5, 'example');

    INSERT INTO rec
    VALUES (2, 1, 'This'), (2, 2, 'is'), (2, 3, 'another'), (2, 4, 'example');

    INSERT INTO rec
    VALUES (3, 1, 'This'), (3, 2, 'example '), (3, 3, 'shows'), (3, 4, 'what'),
    (3, 5, 'can'), (3, 6, 'happen'), (3, 7, 'when'), (3, 8, 'the'),
    (3, 9, 'data'), (3, 10, 'represents'), (3, 11, 'a'), (3, 12, 'really'),
    (3, 13, 'long'), (3, 14, 'line'), (3, 15, 'that'), (3, 16, 'overflows'),
    (3, 17, 'the'), (3, 18, 'length'), (3, 19, 'of'), (3, 20, 'an'),
    (3, 21, 'object');


    WITH rquery (snum, wordnum, sentence)
    AS
    (
    SELECT base.snum, base.wordnum, CAST(base.word AS VARCHAR (2000))
    FROM rec base
    WHERE wordnum = 1

    UNION ALL

    SELECT t1.snum, t1.wordnum, sentence || ' ' || t1.word
    FROM rquery t0, rec t1
    WHERE t0. snum = t1. snum
    AND t0.wordnum + 1 = t1.wordnum
    )
    SELECT *
    FROM rquery rq
    WHERE rq.wordnum = (SELECT max(wordnum) FROM rquery WHERE snum = rq.snum);

    How it should be corrected, or any other way is there to get same output

    Thanks in advance

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The missing output is unrelated to the warning. I don't know how you execute the query but if the tool doesn't recognize warnings as warnings but treats them as errors, your query tools has a bug. Period.

    If you want to get rid of the warning, you can introduce a counter to limit the number of recursive steps. For example, "k" is doing this here: http://www.dbforums.com/db2/1658407-...ml#post6468683
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Nov 2011
    Posts
    4
    Thanks stolze

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The reason of the warning was described clearly by stolze.

    So, you can use wordnum to suppress the warning, like...
    SELECT t1.snum, /*t1.wordnum*/t0.wordnum + 1, sentence || ' ' || t1.word
    FROM rquery t0, rec t1
    WHERE t0. snum = t1. snum
    AND t0.wordnum + 1 = t1.wordnum
    AND t0.wordnum < 100000 /* add */

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You are using DB2 in Unix.
    String concatenation can be done easily on DB2 9.7.4 or later on LUW.

    For example.
    Code:
    SELECT snum
         , LISTAGG(word , ' ')
              WITHIN GROUP(ORDER BY wordnum) AS sentence
     FROM  rec
     GROUP BY
           snum
     ORDER BY
           snum
    ;
    Note: datatype of output sentence would be VARCHAR(4000).

  6. #6
    Join Date
    Nov 2011
    Posts
    4
    thanks tonkuma, for your information.

    I am new to db2/unix, Please can u provide the link where we can learn more about the same.

  7. #7
    Join Date
    Aug 2011
    Posts
    46
    DB2 SQL cook book,

    DB2 SQL Cookbook

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483

Posting Permissions

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