Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2012
    Posts
    5

    Unanswered: Multiple rows as a result from one row in a table

    It's my first post here, I hope everything is clear.

    Columns from the original table.

    Code:
    ID   DATE1       DATE2
    ---------------------------
     1   1960-09-01  1960-11-15
     1   1964-04-06  1964-12-31
     1   1968-06-03  1969-12-31
     2   1961-07-12  1961-10-13
     2   1962-02-01  1963-06-30
     3   1960-09-01  1962-08-31
     3   1962-09-01  1964-12-31
    Result should be like this (I need all years from DATE1 to DATE2 in one column). Is it possible to run it in SPUFI without writing PL/I program?

    Code:
    ID YEAR
    --------
    1  1960
    1  1964
    1  1968
    1  1969
    2  1961
    2  1962
    2  1963
    3  1960
    3  1961
    3  1962
    3  1962
    3  1963
    3  1964
    Thanks for any help. I'm only programmer so I don't have any admin privileges except for writing SELECT/UPDATE/INSERT statements.

    I apologize if something like this was before. I was trying to use search but I didn't get anything that is similar to this.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although I guessed that your DB2 is on z/OS,
    I have no z/OS environment.
    So , I tested on Windows.

    Example 1: tested on DB2 9.7.5 on Windows.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      original_table(ID , DATE1 , DATE2) AS (
    SELECT 1 , '1960-09-01' , '1960-11-15' FROM sysibm.sysdummy1 UNION ALL
    SELECT 1 , '1964-04-06' , '1964-12-31' FROM sysibm.sysdummy1 UNION ALL
    SELECT 1 , '1968-06-03' , '1969-12-31' FROM sysibm.sysdummy1 UNION ALL
    SELECT 2 , '1961-07-12' , '1961-10-13' FROM sysibm.sysdummy1 UNION ALL
    SELECT 2 , '1962-02-01' , '1963-06-30' FROM sysibm.sysdummy1 UNION ALL
    SELECT 3 , '1960-09-01' , '1962-08-31' FROM sysibm.sysdummy1 UNION ALL
    SELECT 3 , '1962-09-01' , '1964-12-31' FROM sysibm.sysdummy1
    )
    , r(id , year , year2) AS (
    SELECT id
         , YEAR(date1)
         , YEAR(date2)
     FROM  original_table
    UNION ALL
    SELECT id
         , year + 1
         , year2
     FROM  r
     WHERE year < year2
       AND year < 9999
    )
    SELECT id
         , year
     FROM  r
     ORDER BY
           id
         , year
    ;
    ------------------------------------------------------------------------------
    
    ID          YEAR       
    ----------- -----------
              1        1960
              1        1964
              1        1968
              1        1969
              2        1961
              2        1962
              2        1963
              3        1960
              3        1961
              3        1962
              3        1962
              3        1963
              3        1964
    
      13 record(s) selected.

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

    Lightbulb Another Solution

    I think the following query will work too:

    Code:
    WITH
      original_table(ID , DATE1 , DATE2) AS (
    SELECT 1 , '1960-09-01' , '1960-11-15' FROM sysibm.sysdummy1 UNION ALL
    SELECT 1 , '1964-04-06' , '1964-12-31' FROM sysibm.sysdummy1 UNION ALL
    SELECT 1 , '1968-06-03' , '1969-12-31' FROM sysibm.sysdummy1 UNION ALL
    SELECT 2 , '1961-07-12' , '1961-10-13' FROM sysibm.sysdummy1 UNION ALL
    SELECT 2 , '1962-02-01' , '1963-06-30' FROM sysibm.sysdummy1 UNION ALL
    SELECT 3 , '1960-09-01' , '1962-08-31' FROM sysibm.sysdummy1 UNION ALL
    SELECT 3 , '1962-09-01' , '1964-12-31' FROM sysibm.sysdummy1
    )
    select t2.id, year(t2.date1)
    from 
    original_table t1
    left join
    original_table t2
    ON  t1.id = t2.id 
    and t2.date1>= t1.date1
    
    UNION
    
    select t2.id, year(t2.date2)
    from 
    original_table t1
    left join
    original_table t2
    ON  t1.id = t2.id 
    and t2.date2>= t1.date2
    ;;;;;;;;;;;;;;;;;;;;;;;;;;;;
    Lenny

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

    Lightbulb The easiest

    Could be much easier then previous:

    Code:
    WITH
      original_table(ID , DATE1 , DATE2) AS (
    SELECT 1 , '1960-09-01' , '1960-11-15' FROM sysibm.sysdummy1 UNION ALL
    SELECT 1 , '1964-04-06' , '1964-12-31' FROM sysibm.sysdummy1 UNION ALL
    SELECT 1 , '1968-06-03' , '1969-12-31' FROM sysibm.sysdummy1 UNION ALL
    SELECT 2 , '1961-07-12' , '1961-10-13' FROM sysibm.sysdummy1 UNION ALL
    SELECT 2 , '1962-02-01' , '1963-06-30' FROM sysibm.sysdummy1 UNION ALL
    SELECT 3 , '1960-09-01' , '1962-08-31' FROM sysibm.sysdummy1 UNION ALL
    SELECT 3 , '1962-09-01' , '1964-12-31' FROM sysibm.sysdummy1
    )
    select id, year(date1)
    from 
    original_table 
    
    UNION
    
    select id, year(date2)
    from 
    original_table 
    ;;;;;;;;;;;;;;;;;;;;;;;;;;;;
    Lenny

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Two issues:
    1) If YEAR(date2) - YEAR(date1) >= 2, then intermittent years wouldn't be in results.
    Both of rows (3, 1961) and (3, 1963) wouldn't be in result.

    2) OP's result duplicate (3, 1962) rows.

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    we used UNION not a UNION ALL. The duplicate is not a possible.

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    About id = 3 you are right ! My query will not work for this.
    Let him use your query....

  8. #8
    Join Date
    Feb 2012
    Posts
    5
    @tonkuma

    Thank you so, so much for the quick answer. It works exactly what I wanted. I had to change values with column names and use right table (it took me some time to find out what change with what ), but it works!!!

    You're right, we have DB2 on z/OS.

    I haven't used values, because (it's my mistake I didn't mention it before) we have many rows with different values (not only the ones I wrote) so I have only one SELECT without UNION not SELECT 1 , '1960-09-01' , '1960-11-15' FROM sysibm.sysdummy1 UNION ALL...

    Another question, if I want to remove duplicates (duplicate years) can I use all of this as subselect? Or is there any more simple way?

    Unfortunately I won't be able to try it until tomorrow, there was an electric shock and the system crashed few minutes ago


    @Lenny77

    Thank you so much to you too, even it doesn't work ok as you wrote, thanks for your effort.
    Quote Originally Posted by Lenny77
    Let him use your query....
    her

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another question, if I want to remove duplicates (duplicate years) ...
    Please try DISTINCT keyword, like...
    Code:
    WITH
      r(id , year , year2) AS (
    SELECT id
         , YEAR(date1)
         , YEAR(date2)
     FROM  original_table
    UNION ALL
    SELECT id
         , year + 1
         , year2
     FROM  r
     WHERE year < year2
       AND year < 9999
    )
    SELECT DISTINCT
           id
         , year
     FROM  r
     ORDER BY
           id
         , year
    ;

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

    Very Easy Solution (my rehabilitation)

    I believe this solution is easy to understand and looks nice:

    Code:
    WITH years_tbl(year) as
    (
    select 1900 from sysibm.sysdummy1 union all
    select year + 1 
    from years_tbl
    where year + 1 <= 2100 
    ) 
    ,  
    original_table(ID , DATE1 , DATE2) AS (
    SELECT 1 , '1960-09-01' , '1960-11-15' FROM sysibm.sysdummy1 UNION ALL
    SELECT 1 , '1964-04-06' , '1964-12-31' FROM sysibm.sysdummy1 UNION ALL
    SELECT 1 , '1968-06-03' , '1969-12-31' FROM sysibm.sysdummy1 UNION ALL
    SELECT 2 , '1961-07-12' , '1961-10-13' FROM sysibm.sysdummy1 UNION ALL
    SELECT 2 , '1962-02-01' , '1963-06-30' FROM sysibm.sysdummy1 UNION ALL
    SELECT 3 , '1960-09-01' , '1962-08-31' FROM sysibm.sysdummy1 UNION ALL
    SELECT 3 , '1962-09-01' , '1964-12-31' FROM sysibm.sysdummy1
    )
    select distinct id, year
    from original_table
    JOIN years_tbl
    on year between year(DATE1) and year(DATE2) 
    ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
    
    Result:

    ID YEAR

    1 1960
    1 1964
    1 1968
    1 1969
    2 1961
    2 1962
    2 1963
    3 1960
    3 1961
    3 1962
    3 1963
    3 1964
    Lenny
    Last edited by Lenny77; 03-01-12 at 10:03.

  11. #11
    Join Date
    Feb 2012
    Posts
    5
    @tonkuma
    It works just perfect! I added few more conditions and tables and it still works (in one moment I really had doubt in my skills ). We've never used something like that before. Once again, thank you for your help.

    @Lenny77
    I've tried your last solution. It looks more simple, really easy to understand and it works, but I get warning that YEARS_TBL may contain an infinitive loop. It's not a problem when you have few rows, but i have about 20M rows. It takes too much time to get result. But thank you anyway for your help. I've learned new things and I'm very glad.

    I apologize for late reply.

Posting Permissions

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