Page 1 of 13 12311 ... LastLast
Results 1 to 15 of 185

Thread: Simple specs

  1. #1
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Cool Unanswered: Simple specs

    Suppose we have table table1 with 2 columns:
    Code:
    Table1( PERSON_ID Varchar(50), project Varchar(50) )
    PERSON_ID project

    0001 A1
    0001 A2
    0001 A3
    0002 A1
    0002 B1
    0003 A1
    0003 A3
    0003 B1
    .......
    .......
    0017
    .......
    .......

    We have to accumulate all projects where person is working in one column of Result Set by each Person.

    The desired output has to be:

    PERSON_ID projects
    0001 (A1,A2,A3)
    0002 (A1,B1)
    0003 (A1,A3,B1)
    ......
    ......
    0017 Null
    ......
    ......

    Lenny
    Last edited by Lenny77; 11-02-09 at 18:32.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You need the aggregation of strings. XMLAGG or recursive queries are the standard answer for that.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2009
    Posts
    114
    with t1 (person_id,project,level,key) as
    (select b1.person_id,b1.project,1,
    varchar(rtrim(b1.person_id)||'/'||rtrim(b1.project)||'/',50)
    from table1 b1
    where (b1.project = (select min(b3.project) from table1 b3
    where b1.person_id=b3.person_id) or b1.project is null)
    union all
    select b2.person_id,b2.project,t1.level+1,
    t1.key||rtrim(b2.project)||'/'
    from table1 b2, t1
    where t1.person_id=b2.person_id and b2.project>t1.project and t1.level<99
    and position('/'||b2.project||'/',t1.key,octets)=0),
    t2 as (select z1.person_id,value(z1.key,rtrim(z1.person_id)||'/') as key from t1 z1
    where z1.level = (select max(z2.level) from t1 z2 where z1.person_id=z2.person_id))
    select key as person_id_projects from t2
    order by 1;

    PERSON_ID_PROJECTS
    --------------------------------------------------
    0001/A1/A2/A3/
    0002/A1/B1/
    0003/A1/A3/B1/
    0017/

    4 record(s) selected.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You need the aggregation of strings. XMLAGG or recursive queries are the standard answer for that.
    If you are using DB2 for LUW 9.5 or later, XMLGROUP may bw simpler.
    Like this:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT VARCHAR(person_id, 10) AS person_id
         , INSERT(
              XMLCAST(
                 XMLGROUP(
                    XMLTEXT(', ' || project) AS a
                    ORDER BY project)
              AS VARCHAR(50) )
           , 1, 2, '(')
           || ')' AS "projects"
      FROM Table1
     GROUP BY
           person_id
    ;
    ------------------------------------------------------------------------------
    
    PERSON_ID  projects                                            
    ---------- ----------------------------------------------------
    0001       (A1, A2, A3)                                        
    0002       (A1, B1)                                            
    0003       (A1, A3, B1)                                        
    0017       -                                                   
    
      4 record(s) selected.

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Quote Originally Posted by db2dummy1 View Post
    with t1 (person_id,project,level,key) as
    (select b1.person_id,b1.project,1,
    varchar(rtrim(b1.person_id)||'/'||rtrim(b1.project)||'/',50)
    from table1 b1
    where (b1.project = (select min(b3.project) from table1 b3
    where b1.person_id=b3.person_id) or b1.project is null)
    union all
    select b2.person_id,b2.project,t1.level+1,
    t1.key||rtrim(b2.project)||'/'
    from table1 b2, t1
    where t1.person_id=b2.person_id and b2.project>t1.project and t1.level<99
    and position('/'||b2.project||'/',t1.key,octets)=0),
    t2 as (select z1.person_id,value(z1.key,rtrim(z1.person_id)||'/') as key from t1 z1
    where z1.level = (select max(z2.level) from t1 z2 where z1.person_id=z2.person_id))
    select key as person_id_projects from t2
    order by 1;

    PERSON_ID_PROJECTS
    --------------------------------------------------
    0001/A1/A2/A3/
    0002/A1/B1/
    0003/A1/A3/B1/
    0017/

    4 record(s) selected.
    Did you use level, as sequence number ?

    Lenny

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by tonkuma View Post
    If you are using DB2 for LUW 9.5 or later, XMLGROUP may bw simpler.
    Like this:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT VARCHAR(person_id, 10) AS person_id
         , INSERT(
              XMLCAST(
                 XMLGROUP(
                    XMLTEXT(', ' || project) AS a
                    ORDER BY project)
              AS VARCHAR(50) )
           , 1, 2, '(')
           || ')' AS "projects"
      FROM Table1
     GROUP BY
           person_id
    ;
    ------------------------------------------------------------------------------
    
    PERSON_ID  projects                                            
    ---------- ----------------------------------------------------
    0001       (A1, A2, A3)                                        
    0002       (A1, B1)                                            
    0003       (A1, A3, B1)                                        
    0017       -                                                   
    
      4 record(s) selected.
    Looks nice !
    Thanks

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow My solution

    Code:
    with Person_in (person_id, proj) as
    (
    select varchar('0001', 50), varchar('A1', 50)
    from sysibm.sysdummy1 
    union all
    select '0001', 'A2'
    from sysibm.sysdummy1 
    union all
    select '0001', 'A3'
    from sysibm.sysdummy1 
    union all
    select '0002', 'A1'
    from sysibm.sysdummy1 
    union all
    select '0002', 'B1'
    from sysibm.sysdummy1 
    union all
    select '0003', 'A1'
    from sysibm.sysdummy1 
    union all
    select '0003', 'A3'
    from sysibm.sysdummy1 
    union all
    select '0003', 'B1'
    from sysibm.sysdummy1 
    union all
    select '0017', nullif('', '')
    from sysibm.sysdummy1 
    union all
    select '0021', 'C5'
    from sysibm.sysdummy1 
    union all
    select '0021', 'C7'
    from sysibm.sysdummy1 
    )
    ,
    Person_dst(person_id, proj) as
    ( 
    select distinct person_id, ifnull(proj, ' ')
    from Person_in 
    )
    ,
    Person_Seq (person_id, proj, seq, Mseq) as
    (
    select person_id, proj, seq, Mseq
    from Person_dst i1, table
    (select count(*) + 1 seq from Person_dst i2
      where i1.person_id || i1.proj > i2.person_id || i2.proj ) ss
    , table
    (select count(*) Mseq from Person_dst) mm
    )
    ,
    Person_Final (person_id, projs, seq, Mseq) as 
    (
    select person_id, varchar( '(' || proj || ')', 2000), 1, Mseq 
      from Person_Seq where seq = 1
    Union All
    select f1.person_id, replace(f1.projs, ')', ', ' || p1.proj || ')' ), 
           f1.seq + 1, f1.Mseq 
      from Person_Seq p1, Person_Final f1
    where p1.seq       = f1.seq + 1
      and p1.person_id = f1.person_id 
      and f1.seq + 1  <= f1.Mseq
    Union All
    select p1.person_id, '(' || p1.proj || ')', 
           f1.seq + 1, f1.Mseq 
      from Person_Seq p1, Person_Final f1
    where p1.seq        = f1.seq + 1
      and p1.person_id <> f1.person_id 
      and f1.seq + 1   <= f1.Mseq
    ) 
    ,
    Person_Result (person_id, projects) as
    (select  person_id, 
             case when projs = '( )' then '-----' else projs end 
      from Person_Final f2  
       where f2.seq = (select max(f3.seq) from Person_Final f3
                        where f3.person_id = f2.person_id      )
    )
    select * from Person_Result
    Result:

    PERSON_ID PROJECTS

    0001 (A1, A2, A3)
    0002 (A1, B1)
    0003 (A1, A3, B1)
    0017 -----
    0021 (C5, C7)
    Lenny

  8. #8
    Join Date
    Feb 2009
    Posts
    114
    Quote Originally Posted by Lenny77 View Post
    Looks nice !
    Thanks
    Ha-ha-ha!! XML functions only work on toy databases !!

    Look here:

    SELECT VARCHAR(person_id, 10) AS person_id , INSERT( XMLCAST( XMLGROUP( XMLTEXT(', ' || project) AS a ORDER BY project) AS VARCHAR(50) ) , 1, 2, '(') || ')' AS "projects" FROM Table1 GROUP BY person_id
    SQL1239N pureXML data store features can be used only in a single-partition
    database. SQLSTATE=42997

    What a joke!!

    Looks nice!!

  9. #9
    Join Date
    Feb 2009
    Posts
    114
    Quote Originally Posted by Lenny77 View Post
    Did you use level, as sequence number ? Lenny
    Not sure about "sequence number" ... Level is a more appropriate term here because of the tree-traversing nature of this query (pекурсивный алгоритм построения дерева)

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by db2dummy1 View Post
    Not sure about "sequence number" ... Level is a more appropriate term here because of the tree-traversing nature of this query (pекурсивный алгоритм построения дерева)
    Ok, I understood. This is like deepness...

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SQL1239N pureXML data store features can be used only in a single-partition
    database. SQLSTATE=42997
    I guessed that DB2 Version 9.7 for LUW supports pureXML in a partitioned database environment.
    Because, I found the following description in "IBM DB2 9.7 for Linux, UNIX, and Windows Message Reference Volume 2"
    Code:
    SQL1239N   On DB2 database servers Version 9.5
               and earlier, pureXML data store features
               can be used only in a single-partition
               database.

  12. #12
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow Next spec

    In Table1 we have one column: Name Varchar(50).

    Using distinct, only, sort Result Set with only this column in descending order.

    Lenny

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Ha-ha-ha!! XML functions only work on toy databases !!

    .....

    What a joke!!
    db2dummy1,
    Why are you so sarcastic and scathing?
    Your attitude would be bad for constructive discussions.
    Some persons(including me) may hesitate to join the threads which you joined.

    Do you know how much percentage of DB2 installations using partitioned database?
    I thought it would be rather small percentage.

  14. #14
    Join Date
    Oct 2009
    Posts
    24
    ....................

  15. #15
    Join Date
    Feb 2009
    Posts
    114
    Quote Originally Posted by Lenny77 View Post
    Ok, I understood. This is like deepness...
    Yes - depth!

Posting Permissions

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