sql temp table giving wrong results

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

sql temp table giving wrong results

Hi allI

I  have sub query which giving numbers correct.

but when i  insert data into that  temp table and query that table temp table ,i am getting wrong numbers.

please can you help whts going wrong in sql.

 

Select Pref_cd, count(*)   from
  (select
t5.prd_num,
P.Pref_cd

 FROM   test 1                       T5    left join
                 test 2  Fact 
                 on T5.card_num = Fact.card_num                                left  join  
    (SELECT  distinct(cust_num), type_cd, stype_cd, Pref_cd, MAX(a.eff_dt)
                    FROM  BMGPCUST.CUST_PREF_DY_TMPRL_CDB A
                     where
                         a.type_cd='LANG' 
                        AND a.eff_dt <=  '2017-11-30'
                        AND a.expr_dt <=  '9999-12-31'
                       GROUP BY 1,2,3,4) P
       on FACT.cust_crdhd_num = p.cust_num
       WHERE Fact.CARD_IN =1
           and Fact.date = 201711
group by 1 ,2 )A
group by 1

 

if i ran internal query i am getting correct count

 

select
P.Pref_cd,count(*)

 FROM   test 1                       T5    left join
                 test 2  Fact 
                 on T5.card_num = Fact.card_num                                left  join  
    (SELECT  distinct(cust_num), type_cd, stype_cd, Pref_cd, MAX(a.eff_dt)
                    FROM  BMGPCUST.CUST_PREF_DY_TMPRL_CDB A
                     where
                         a.type_cd='LANG' 
                        AND a.eff_dt <=  '2017-11-30'
                        AND a.expr_dt <=  '9999-12-31'
                       GROUP BY 1,2,3,4) P
       on FACT.cust_crdhd_num = p.cust_num
       WHERE Fact.CARD_IN =1
           and Fact.date = 201711
group by 1

 

if i ran from temp table same count i am getting wrong counts ...

please can you help on this.

 

3 REPLIES
Teradata Employee

Re: sql temp table giving wrong results

Not sure of your question.

 

If there are multiple prd_num for one card_num, then the results will be different. Perhaps compute COUNT(*) AS PartialCount in the inner query and SUM(A.PartialCount) AS TotalCount in the outer query?

Highlighted
Junior Contributor

Re: sql temp table giving wrong results

Your Select probably returns duplicate rows and the temp table is defined as SET removing those duplicates. Try MULTISET instead.

Enthusiast

Re: sql temp table giving wrong results

ok