Topic
2 replies Latest Post - ‏2012-06-21T14:20:36Z by ToddBurchDB2
Jotac
Jotac
3 Posts
ACCEPTED ANSWER

Pinned topic wrong result with group by/order by

‏2012-06-21T13:55:39Z |
Hello

DB2 z/OS 10.1.2

what is wrong here?!

Given this select :
SELECT T66.F1
, COUNT(DISTINCT T66.F2) AS R1
, DECIMAL(SUM( T66.F3 *T97.F4) ,15,2) AS R2
, DECIMAL(SUM((T66.F5 +T66.F6) *T97.F4),15,2) AS R3
, DECIMAL(SUM( T66.F7 *T97.F4) ,15,2) AS F7
, DECIMAL(SUM( T66.F8 *T97.F4) ,15,2) AS R4
FROM DB.TABLE1 T74
, DB.TABLE2 T66
, DB.TABLE3 T01
, DB.TABLE4 T97
WHERE T74.F5 = 'xxxxxxx'
AND T74.F7 = 'yy'
AND T66.F1 BETWEEN T74.F1 +1 DAY -24 MONTHS AND T74.F1
AND T66.F8 IN (0,1,3)
AND T01.F2 = T66.F2
AND T01.F9 = 8690
AND T97.F10 = T66.F10
GROUP BY T66.F1
ORDER BY T66.F1

I get different number of row as result set, sometime 40 row sometime 44 row sometime 41 row and so on.
Never see that?

2nd...

If I change the field list on the result table the number of row returned is stable to 24 rows.

ciao
GIovanni
Updated on 2012-06-21T14:20:36Z at 2012-06-21T14:20:36Z by ToddBurchDB2
  • Jotac
    Jotac
    3 Posts
    ACCEPTED ANSWER

    Re: wrong result with group by/order by

    ‏2012-06-21T14:11:09Z  in response to Jotac
    forgot to point-out that all input tables are stable, nobody insert nor delete records.

    GIovanni
    • ToddBurchDB2
      ToddBurchDB2
      74 Posts
      ACCEPTED ANSWER

      Re: wrong result with group by/order by

      ‏2012-06-21T14:20:36Z  in response to Jotac
      Hi Giovanni.

      This might be a defect. Check the EXPLAIN information for your queries and see if you can determine if a particular access path is common across all variations of your query. Then, check the access path of the proper result set query. If you can narrow it down to a particular access path, that would be great, and then report a defect, supplying the Service SQL for this issue.

      Thanks, Todd (DB2 L2)