Create table as select counts vs select counts different?

Database

Create table as select counts vs select counts different?

I am new to Teradata but have run into something strange:

I run a select that gets one set of counts and when I put that same select into a "create table as select" my counts end up being different.  What am I missing?

If I run the following select:

select MKTNG_PGM_NBR, count(MATCHD_CNSMR_PRSNA_ID)

from

(                                                              

select                                                 

 RP.MATCHD_CNSMR_PRSNA_ID,         

 CA.CNSMR_ACTN_TYPE_CODE,           

 CA.MKTNG_PGM_NBR,                              

 EXTRACT(MONTH FROM CA.CNSMR_ACTN_START_DATETM)  as month1 ,

CA.CNSMR_ACTN_START_DATETM    

from  CRM.REGIS_PRSNA RP                

 INNER JOIN CRM.CNSMR_ACTN CA                 

 ON  RP.REGIS_PRSNA_ID= CA.REGIS_PRSNA_ID   

 AND RP.MKTNG_PGM_NBR=CA.MKTNG_PGM_NBR   

 WHERE CAST(CNSMR_ACTN_START_DATETM AS DATE) >= '2013-01-01'  

 AND CAST(CNSMR_ACTN_START_DATETM AS DATE) <= '2013-03-31' 

 and CA.MKTNG_PGM_NBR in (111)  

 and CA.LEGAL_ENT_NBR in(15,16)       

 and CA.CNSMR_ACTN_TYPE_CODE in ('CR')                                                   

) a

group by MKTNG_PGM_NBR;

My result is 115   13,830

If I run the same selct within a create table as I am getting different counts:

create table tmp_loe_test_sept_cr as

(                              

select                                                 

 RP.MATCHD_CNSMR_PRSNA_ID,         

 CA.CNSMR_ACTN_TYPE_CODE,           

 CA.MKTNG_PGM_NBR,                              

 EXTRACT(MONTH FROM CA.CNSMR_ACTN_START_DATETM)  as month1 ,

CA.CNSMR_ACTN_START_DATETM    

from  CRM.REGIS_PRSNA RP                

 INNER JOINiCRM.CNSMR_ACTN CA                 

 ON  RP.REGIS_PRSNA_ID= CA.REGIS_PRSNA_ID   

 AND RP.MKTNG_PGM_NBR=CA.MKTNG_PGM_NBR   

 WHERE CAST(CNSMR_ACTN_START_DATETM AS DATE) >= '2013-01-01'  

 AND CAST(CNSMR_ACTN_START_DATETM AS DATE) <= '2013-03-31' 

 and CA.MKTNG_PGM_NBR in (111)  

 and CA.LEGAL_ENT_NBR in(15,16)       

 and CA.CNSMR_ACTN_TYPE_CODE in ('CR')                                                                   

)

with data;

select MKTNG_PGM_NBR, count(MATCHD_CNSMR_PRSNA_ID)

from tmp_loe_test_sept_cr

group by MKTNG_PGM_NBR

My result is 115   6,673

Why am I getting different counts?  In theory they shuold return the exact same thing.

Thanks

2 REPLIES
Junior Contributor

Re: Create table as select counts vs select counts different?

Probably: The select returned duplicate rows and your table is created as SET table, do a SHOW TABLE MKTNG_PGM_NBR;

If you actually need the duplicates you can override it:CREATE MULTISET TABLE ...

And as you didn't specify a Primary Index it will default to a NUPI on the 1st column MATCHD_CNSMR_PRSNA_ID, you should check if this is acceptable.

Dieter

Re: Create table as select counts vs select counts different?

Thanks!  That is what was happening - duplicate rows were being deleted.  I created the multiset table and preserved rows on commit and I got the results I was expecting.