Archives of the TeradataForum

Message Posted: Tue, 24 Nov 2009 @ 22:05:14 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Both errors are pretty specific, so I don't know how much it'll help folks here, but:

1) A query sometimes produces incorrect results for a specific set of rows. This one may be related to ambiguous aliasing, but we haven't gotten a final diagnosis yet. It selects from an inner query using qualify row_number() over (partition by 2 fields order by 1) = 1, but when it runs, it sometimes returns no rows for a specific OLAP partition. We can see that in that one partition (and we've only ever seen this for this one partition out of over a million) the query sometimes generates row_number() of 1 and 2, but sometimes it has 2 and 3, 2 and 4, or 3 and 4. In these latter cases, no rows are returned. I guess all I can suggest is never use aliases that are also field names in any underlying table, even if the aliases aren't used until an outer layer of nested queries, but even that might not be enough.

2) I have a query with an inline view that uses CASE to map detailed product types to a two-level dimensional rollup, with ELSE 'other' at the end of the lower level. These then get summed with a group by cube(). However, for rows where the top level of the dimension are summed (grouping = 1), some of the lower level members become 'other' rather than what they were in the detail rows. Sort of like

     Top level  lower level  value
     A          1              1
     A          2              1
     B          3            100
     B          4            100
     B          5            100
     NULL       other          2
     NULL       3            100
     NULL       4            100
     NULL       5            100
     A          NULL           2
     B          NULL         300
     NULL       NULL         302

Since using inline views is pretty standard practice, I can't think of anything to suggest here. Maybe just be sure to double-check your totals if you use CUBE, ROLLUP or GROUPING SETS.

Ironically, I wrote the second query literally minutes after commenting to somebody about the first one that I'd rather have a query be consistently wrong than sometimes right and sometimes wrong. I think now that I dislike them both equally.

Dave

	<Prev	Next>		<<First	<Prev	Next>	Last>>