Archives of the TeradataForum

Message Posted: Tue, 23 Aug 2005 @ 17:08:48 GMT

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

All,

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:

PK_col1
PK_col2
TypeCode (to describe what "type" the record is)
Date
Time
Attribute1
Attribute2

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:

TypeCode
Date
Time
Attribute1
Attribute2
TypeCount (basically just the count of records. Originally it was hard-coded as 1 as there was no grouping performed, but MicroStrategy likes to have something to sum).

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.

Mike

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