update statement not working as expected

Database
Enthusiast

update statement not working as expected

Hi All,

I am doing this update statement. Its bringing odd data. do i am doing any thing wrong. please can you help on this.

 

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 20762 StartFragment: 314 EndFragment: 20730 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

create VOLATILE  table test1 as    
( SELECT      
    dateid,
      au_num 
     
FROM ACCT_FACT
where dateid = 201705)with data primary index (dateid ,
      au_num 
      
     )ON COMMIT PRESERVE ROWS;

create VOLATILE  table test2  as     -( SELECT
      dateid ,
      au_num ,
  
      sum(dollar)   AS dollar,
      sum( amount) AS  amount
FROM( SELECT DISTINCT AU_NUM 
              
               ,a.dateid    
                
                 ,sum(case when b.clicks in (6902, 5775,1240, 5671) then 1 else 0 end) as dollar, 
                sum(case when b.clicks in (6902, 5775, 1240, 5671) then tran_amt else 0 end) as amount
                
from
test3  a  -- this fact table has only 201705 data.join  test4 b 
     on a.acct_num = b.acct_num
    and a.co_id = b.co_id
    and a.dateid = b.dateid
left outer join test5   c   
on b.clicks = c.clicks
where b.clicks in (5815,5816,5817,5818,5839,5840,5845,5846,5847,5848,6458,6460,6866,6867,6894,6895,6904,6905,6906,6907,6971,6960,6961,6962,6963,6902,5775,1240,5671) AND 
group by 1,2) fact
GROUP BY 1,2 
with data primary index (dateid                ,au_num    )ON COMMIT PRESERVE ROWS;

Update A from 
ACCT_FACT A, 
(SELECT A.dateid,B.au_num,B.dollar, 
                               B.amount
FROM test 1    A
full outer JOIN  test2     B
ON A.dateid = B.dateid
AND A.au_num         = B.au_num
/
where B.clicks > 0  -)   B

 SET  clicks=b.clicks,
           amount=b.amount
            
 WHERE  
A.dateid = B.dateid
AND A.au_num         = B.au_num 
;

 

2 REPLIES
Teradata Employee

Re: update statement not working as expected

Since the above is not quite valid SQL, and you describe neither what is "expected" nor the "odd data", it's hard to provide much help.

Enthusiast

Re: update statement not working as expected

for ex:  au_num = 2  has dollar value  is 800 and amount is 1000 ,  then in update set statement should updated with that values. Its showing some number it is  not at all  matching.