Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2016
    Posts
    3

    Post Unanswered: db2 7.1 zos query col to row [no pivot]

    Hi,
    I present myself: Emiliano from Rome, developer and db2 beginner.

    I've got a problem: I've made a webpage with a gridview that present some datas fetched by a query on a DB2 dbase.

    The query from 2 tables with his answer is this one:

    The Query:
    Code:
    select a.COL1, a.COL2, a.COL3, a.COL4,  b.COL5 from db2.tb1 a, db2.tb2 b where  a.COL1 = b.COL1 and  a.COL1 = "VAL1"
    The Answer:
    Code:
    COL1     COL2     COL3     COL4     COL5
    VAL1     VAL2     VAL3     VAL4     PRB1
    VAL1     VAL2     VAL3     VAL4     PRB2
    And due to the fact that this query is the base to populate a grid with this data I want to present my result in this other way:

    Code:
    COL1     COL2     COL3     COL4     COL5
    VAL1     VAL2     VAL3     VAL4     PRB1,PRB2
    maybe I'm just stupid but I had a lot of difficulties in implement recursive queries and the DB2 version we got does not support XML nor LISTAGG.

    Any suggestion?

    Thanks in advance
    Emiliano

  2. #2
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    All I can say is .... 7.1! That went EOS in 2008!
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi Emiliano,

    If your version supports rownumber OLAP function and RCTEs, you can try this:
    Code:
    with a(COL1, COL2, COL3, COL4, COL5) as (
      select 'VAL1', 'VAL2', 'VAL3', 'VAL4', 'PRB1' from sysibm.sysdummy1
        union all
      select 'VAL1', 'VAL2', 'VAL3', 'VAL4', 'PRB2' from sysibm.sysdummy1
    )
    , b as (select a.*, rownumber() over() rn_ 
    from a
    )
    , c (rn_, COL1, COL5) as (
    select
      rn_ 
    , CAST(','||COL1||',' AS VARCHAR(128)) COL1
    , CAST(','||COL5||',' AS VARCHAR(128)) COL5
    from b
    where rn_=1
      union all
    select
      b.rn_ 
    , c.COL1||case when locate(','||b.COL1||',', c.COL1)=0 then b.COL1||',' else '' end
    , c.COL5||case when locate(','||b.COL5||',', c.COL5)=0 then b.COL5||',' else '' end
    from b, c
    where b.rn_=c.rn_+1
    )
    select
      substr(COL1, 2, LENGTH(COL1)-2) COL1
    , substr(COL5, 2, LENGTH(COL5)-2) COL5
    from c
    where rn_=(select max(rn_) from c)
    Regards,
    Mark.

  4. #4
    Join Date
    Feb 2016
    Posts
    3

    Question

    Quote Originally Posted by tafster View Post
    All I can say is .... 7.1! That went EOS in 2008!
    It's not a choice of mine. I found the DB here and I have to work with that.


    Quote Originally Posted by mark.b View Post
    Hi Emiliano,

    If your version supports rownumber OLAP function and RCTEs, you can try this:
    Code:
    with a(COL1, COL2, COL3, COL4, COL5) as (
      select 'VAL1', 'VAL2', 'VAL3', 'VAL4', 'PRB1' from sysibm.sysdummy1
        union all
      select 'VAL1', 'VAL2', 'VAL3', 'VAL4', 'PRB2' from sysibm.sysdummy1
    )
    , b as (select a.*, rownumber() over() rn_ 
    from a
    )
    , c (rn_, COL1, COL5) as (
    select
      rn_ 
    , CAST(','||COL1||',' AS VARCHAR(128)) COL1
    , CAST(','||COL5||',' AS VARCHAR(128)) COL5
    from b
    where rn_=1
      union all
    select
      b.rn_ 
    , c.COL1||case when locate(','||b.COL1||',', c.COL1)=0 then b.COL1||',' else '' end
    , c.COL5||case when locate(','||b.COL5||',', c.COL5)=0 then b.COL5||',' else '' end
    from b, c
    where b.rn_=c.rn_+1
    )
    select
      substr(COL1, 2, LENGTH(COL1)-2) COL1
    , substr(COL5, 2, LENGTH(COL5)-2) COL5
    from c
    where rn_=(select max(rn_) from c)
    Hi, thank you. I want to learn how it works, can you explain this? I mean the whole query.

    And furthermore I see in the first rows:

    Code:
    with a(COL1, COL2, COL3, COL4, COL5) as (
      select 'VAL1', 'VAL2', 'VAL3', 'VAL4', 'PRB1' from sysibm.sysdummy1
        union all
      select 'VAL1', 'VAL2', 'VAL3', 'VAL4', 'PRB2' from sysibm.sysdummy1
    )
    is this line limiting the result on the values I wrote? Just because in my example the where clause is stating only

    Code:
     [...] a.COL1=VAL1
    to filter the results. and I'll use this filter to create new results depending on user actions.

    Thanks in advance.

    Emiliano
    Last edited by ebaccini; 02-24-16 at 04:59. Reason: typo

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    I wrote this trying to construct a fully working solution without an access to your tables. You can run it as is just for test.
    Does it work at all?

    If not, what's the error?

    If it does work, then instead of:
    Code:
    with a(COL1, COL2, COL3, COL4, COL5) as (
      select 'VAL1', 'VAL2', 'VAL3', 'VAL4', 'PRB1' from sysibm.sysdummy1
        union all
      select 'VAL1', 'VAL2', 'VAL3', 'VAL4', 'PRB2' from sysibm.sysdummy1
    )
    , b as (select a.*, rownumber() over() rn_ 
    from a
    )
    you can probably try this for your query:
    Code:
    with b as (
    select a.COL1, a.COL2, a.COL3, a.COL4,  b.COL5 
    , rownumber() over() rn_
    from db2.tb1 a, db2.tb2 b where  a.COL1 = b.COL1 and  a.COL1 = "VAL1"
    )
    Regards,
    Mark.

  6. #6
    Join Date
    Feb 2016
    Posts
    3
    Quote Originally Posted by mark.b View Post
    I wrote this trying to construct a fully working solution without an access to your tables. You can run it as is just for test.
    Does it work at all?

    If not, what's the error?

    If it does work, then instead of:
    Code:
    with a(COL1, COL2, COL3, COL4, COL5) as (
      select 'VAL1', 'VAL2', 'VAL3', 'VAL4', 'PRB1' from sysibm.sysdummy1
        union all
      select 'VAL1', 'VAL2', 'VAL3', 'VAL4', 'PRB2' from sysibm.sysdummy1
    )
    , b as (select a.*, rownumber() over() rn_ 
    from a
    )
    you can probably try this for your query:
    Code:
    with b as (
    select a.COL1, a.COL2, a.COL3, a.COL4,  b.COL5 
    , rownumber() over() rn_
    from db2.tb1 a, db2.tb2 b where  a.COL1 = b.COL1 and  a.COL1 = "VAL1"
    )

    Hi,
    I've tried both and I receive this error:

    ERROR CODE:-199, SQLSTATE:42601, SQLERRMC:AS;IS <HEXSTRING> <CHARSTRING> <GRAPHSTRING>

    To help to fix put my problem I want to explain something: the quesy I send to have the result is:

    Code:
    select a.COL1, a.COL2, a.COL3, a.COL4,  b.COL5 from db2.tb1 a, db2.tb2 b where  a.COL1 = b.COL1 and  a.COL4 = "VAL4"
    and the result is:

    Code:
    COL1     COL2     COL3     COL4     COL5
    VAL1     VAL2     VAL3     VAL4     PRB1
    VAL1     VAL2     VAL3     VAL4     PRB2
    VAL5     VAL6     VAL7     VAL4     PRB1
    VAL8     VAL9     VAL0     VAL4     PRB1
    VAL10    VAL11    VAL12    VAL4     PRB2
    VAL13    VAL14    VAL15    VAL4     PRB1
    VAL13    VAL14    VAL15    VAL4     PRB2
    And what I'm looking for is:

    Code:
    COL1     COL2     COL3     COL4     COL5
    VAL1     VAL2     VAL3     VAL4     PRB1,PRB2
    VAL5     VAL6     VAL7     VAL4     PRB1
    VAL8     VAL9     VAL0     VAL4     PRB1
    VAL10    VAL11    VAL12    VAL4     PRB2
    VAL13    VAL14    VAL15    VAL4     PRB1,PRB2
    Hope it clarify what I'm looking for.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    If you are really on V7 of DB2 on a Z/OS machine, there is no SQL solution to give you the requested result set. If at most you would ever have is a set number of occurrences of COL5, you could do them as additional columns COL6, COL7, etc... by just performing that number of LEFT OUTER JOINS to get the subsequent occurrences of values. To do this with an SQL solution such as Mark has provided to you, you would have to get someone to upgrade DB2 a few times(2, maybe even 3 newer versions anyway). Good luck.
    Dave

Tags for this Thread

Posting Permissions

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