Archives of the TeradataForum
Message Posted: Tue, 23 Aug 2005 @ 17:08:48 GMT
I had something odd happen last week that I wanted some input on.
I have a table:
CREATE MUTLISET TABLE tablename, NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL ( PK_col1 DECIMAL(12,0), PK_col2 VARCHAR(5), TypeA_date DATE, TypeA_time TIME, TypeA_attribute1 CHAR(5), TypeA_attribute2 CHAR(5), TypeB_date DATE, TypeB_time TIME, TypeB_attribute1 CHAR(5), TypeB_attribute2 CHAR(5), ... ) PRIMARY INDEX (PK_col1, PK_col2);
I then have a view that basically normalizes this table so it looks like this:
This is done using multiple UNION statements (I've since then found a more efficient way to perform the view but haven't changed the view yet and don't know whether the situation that I describe below still occurs).
There is then another view on top of that view that summarizes the records:
As you can see, the last step in the process no longer has the original primary key. This isn't all that problematic to me as the other views are utilized to report on the granular data, this view is only to report on the different summarizations (and is needed for other reasons that aren't apparent from the description above).
If two records have the same TypeCode, Date, Time, Attribute1, and Attribute2 (unlikely, but it does happen), I would have expected 2 records to have been returned by this view. However, the view actually only returns 1 record.
Does anyone have any idea of why this happens and if it's expected behavior? (It certainly wasn't expected behavior for me!) We fixed the problem by turnin the TypeCount into a true count(*) and grouping by all columns, but it still bugged me as to why it happened that way.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|