Archives of the TeradataForum

Message Posted: Tue, 29 Jul 2008 @ 12:57:25 GMT

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

Hi all,

Table1 = rpt_member

Table2 = rpt_update_temp

The query returns the same results whether I use the "update <alias> from table1, table2" or the "update table1 from table2" structure. But yes, thanks for catching that typo in the SET clause.

The rpt_member_id field is the PI and is defined as not null on both tables. But just to be safe, I verified that the counts for both queries below return 0.

Table def for RPT_MEMBER:

     CREATE SET TABLE rpt_member ,NO FALLBACK ,
        NO BEFORE JOURNAL,
        NO AFTER JOURNAL,
        CHECKSUM = DEFAULT
        (
         RPT_MEMBER_ID DECIMAL(12,0) NOT NULL GENERATED ALWAYS AS IDENTITY
              (START WITH 1
               INCREMENT BY 1
               MINVALUE -999999999999
               MAXVALUE 999999999999
               NO CYCLE),
         ....
               CONSUMER_COUNT INTEGER NOT NULL DEFAULT 0 ,
               CREATE_TMS TIMESTAMP(6) NOT NULL,
               CREATE_USER_ID VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
               UPDATE_TMS TIMESTAMP(6) NOT NULL,
               UPDATE_USER_ID VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
        PRIMARY INDEX ( RPT_MEMBER_ID );

Table def for RPT_UPDATE_TEMP

     CREATE SET TABLE rpt_member ,NO FALLBACK ,
        NO BEFORE JOURNAL,
        NO AFTER JOURNAL,
        CHECKSUM = DEFAULT
        (
         RPT_MEMBER_ID DECIMAL(12,0) NOT NULL ,
         CONSUMER_COUNT INTEGER NOT NULL DEFAULT 0)
        PRIMARY INDEX ( RPT_MEMBER_ID );

I pulled back records from rpt_member where the rpt_member_id was not in rpt_update_temp:

     SELECT rpt_member_id
     FROM rpt_member
     WHERE rpt_member_id NOT IN
               (SELECT rpt_member_id FROM rpt_update_temp GROUP BY 1)
       AND CONSUMER_COUNT>0
     GROUP BY 1

-- The CONSUMER_COUNT > 0 is meant to eliminate rows in rpt_member that I -- know will not have a corresponding rpt_update_temp record.

But every rpt_member_id resulting from this query has shown to be in rpt_update_temp when I query table2 for the table1_id directly.

I sampled the first few rpt_member_ids, but can see that they are present in the rpt_update_temp table

e.g.

     select * from rpt_update_temp where
     rpt_member_id in
     (326,
     387,
     427,
     448,
     469,
     488,
     509);

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