Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2009
    Posts
    8

    Question Unanswered: joining to a 'fetch first 20' temp table

    1st post:
    I have a query that pulls the first 20 rows of instances
    (Select id, name from T1 fetch first 20 rows only)

    If I pull info from another table against the TEMP table I get 20 rows.
    (select TEMP.id, T2.Stuff from TEMP left join T2 on TEMP.ID = T2.ID).

    HOWEVER, If I run that join again I get 600 records:

    Select id, name, D1.Stuff from TEMP
    left join (
    select TEMP.id, T2.Stuff from TEMP left join T2 on TEMP.ID = T2.ID
    ) D1 on TEMP.id = D1.ID

    When I remove the 'fetch first 20 rows only' , then I get all row counts the same from TEMP to final pull. Is this ia quirk in DB@ or am I missing something?

  2. #2
    Join Date
    Nov 2009
    Posts
    8
    There are no dups in any of the result sets.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by CandyMan View Post
    am I missing something?
    May be you are, but it's hard to tell without seeing the actual query.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Similar on delirium.
    But, if you have the DB2 V9, you can apply "fetch first 20 rows only" inside of subselects.

    Lenny

  5. #5
    Join Date
    Nov 2009
    Posts
    8
    if I take LEFT JOIN SQL from below and insert it just below temp, run TEMP and that section only, I get 20 records.



    with TEMP As (
    Select
    MA.ID
    ,cast(MA.ACCT_NBR as varchar(16)) ACCTNBR
    ,MA.AMT
    ,h.APV_DT
    from MyTables.MyAccounts MA
    join MyTables.AccountHist H
    on MA.ID = h.ID
    where
    h.APV_DT >= '2009-09-01 00:00:00'
    and h.APV_DT < '2009-10-01 00:00:00'
    fetch first 20 rows only
    )





    SELECT
    TEMP.ID,
    TEMP.AMT,
    TEMP.APV_DT,
    TEMP.acctnbr
    D1.Curr_Bal
    FROM temp
    LEFT JOIN
    (
    SELECT
    TEMP.ID
    , BAL.CreditLine
    , int (BAL.Currbal) Curr_Bal
    FROM temp
    inner JOIN MyTables.AccountBal BAL
    ON temp.acctnbr = BAL.acctnbr
    and TEMP.APV_DT = BAL.TIME_ID

    ) D1
    on temp.ID = D1.ID

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    So, how many rows does that return?
    Code:
    SELECT 
     TEMP.ID
     , BAL.CreditLine
     , int (BAL.Currbal) Curr_Bal 
    FROM temp
    inner JOIN MyTables.AccountBal BAL
     ON temp.acctnbr = BAL.acctnbr
     and TEMP.APV_DT = BAL.TIME_ID
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Nov 2009
    Posts
    8
    600 and no dups. I've never seen this before. If I remove the fetch statement then I get say 1000 records in TEMP and 1000 as the finial record set. The count is the same.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by CandyMan View Post
    600
    There you go. Join 600 and 20 records with matching IDs and you get 600 records.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Nov 2009
    Posts
    8
    No, that is not it. It has to be with the FETCH. and sometihing in memory. I still get 600 records.

    If i simplify the SQL to :
    with TEMP As (
    Select
    MA.ID
    ,MA.AMT
    from MyTables.MyAccounts MA
    where
    fetch first 20 rows only
    )

    SELECT
    TEMP.ID,
    TEMP.AMT,
    T.ID T_ID
    FROM temp
    LEFT JOIN
    TEMP T
    on TEMP.ID = T.ID

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    If u simplify the SQL to (?):

    with TEMP As (
    Select
    ID, AMT
    from MyTables.MyAccounts
    )

    SELECT
    t1.ID,
    t1.AMT,
    T2.ID T_ID
    FROM temp t1
    LEFT JOIN
    TEMP t2
    on t1.ID = t2.ID
    fetch first 20 rows only


    Lenny

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by CandyMan View Post
    No, that is not it. It has to be with the FETCH. and sometihing in memory.
    If you say so. However, I do not have this problem:
    Code:
    #cat t.sql
    with bigtable (d,l) as (
     select cast(rand() as decimal(10,2)) ,1 from sysibm.sysdummy1 t1
      union all
     select cast(rand() as decimal (10,2)), t.l+1
      from sysibm.sysdummy1 t2, bigtable t
      where  t.l < 50
    ),
    temp (val, key) as (
     select d, l from bigtable fetch first 5 rows only
    )
    select temp.key, temp.val, t.key 
    from temp 
     left join
    temp t 
     on t.key = temp.key
    ;
    #db2 -tf t.sql
    
    KEY         VAL          KEY
    ----------- ------------ -----------
              1         0.00           1
              2         0.65           2
              3         0.30           3
              4         0.67           4
              5         0.10           5
    
      5 record(s) selected.
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Nov 2009
    Posts
    8
    Memory solution:
    rename TEMP to PreTEMP
    add: ,with TEMP as(Select ID, AMT from PreTEMP)
    Results: 20 records.
    Why?

  13. #13
    Join Date
    Nov 2009
    Posts
    8
    Memory solution:
    rename TEMP to PreTEMP
    add: ,with TEMP as(Select ID, AMT from PreTEMP)
    Results: 20 records.
    Why?

  14. #14
    Join Date
    Nov 2009
    Posts
    8
    Interesting.
    My Memory solution:
    rename TEMP to PreTEMP
    add: ,with TEMP as(Select ID, AMT from PreTEMP)
    Results: 20 records.
    Go figure

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
  •