joining to a 'fetch first 20' temp table

CandyMan · #1 (**permalink**) 11-18-09, 16:16

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?

CandyMan · #2 (**permalink**) 11-18-09, 16:17

There are no dups in any of the result sets.

n_i · #3 (**permalink**) 11-18-09, 16:47

Quote:

Originally Posted by CandyMan

am I missing something?

May be you are, but it's hard to tell without seeing the actual query.

Lenny77 · #4 (**permalink**) 11-18-09, 17:01

Similar on delirium.

But, if you have the DB2 V9, you can apply "fetch first 20 rows only" inside of subselects.

Lenny

CandyMan · #5 (**permalink**) 11-18-09, 18:36

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

n_i · #6 (**permalink**) 11-18-09, 22:02

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

CandyMan · #7 (**permalink**) 11-19-09, 11:00

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.

n_i · #8 (**permalink**) 11-19-09, 11:22

Quote:

Originally Posted by CandyMan

600

There you go. Join 600 and 20 records with matching IDs and you get 600 records.

CandyMan · #9 (**permalink**) 11-19-09, 11:59

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

Lenny77 · #10 (**permalink**) 11-19-09, 12:15

If u simplify the SQL to (?):

Quote:

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

n_i · #11 (**permalink**) 11-19-09, 12:21

Quote:

Originally Posted by CandyMan

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.