syntax help

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

syntax help

Hi aall,

 

Need syntax help on temp tables functionalities:

i have this table

 

create VOLATILE  table EE as
 (  SELECT card_num, CARD_READ_CD,
  
    sum(case when (ee.CARD_READ_CD in ('1','2','3','4','8')  or ee.CARD_READ_CD is null)   then 1 else 0 end) as READCNT ,

from fact201711      EE

group by card_num,2
)
 with data primary index (card_num )
ON COMMIT PRESERVE ROWS;


select card_num,CARD_READ_CD,sum( READCNT )   from EE
group by 1 ,2

I am getting 400k sum

 

 

when i write same sql as

 

 

create VOLATILE  table EE  as
 (  SELECT card_num,

CAST(CARD_READ_CD as int) AS CARD_READ_CD
case when CARD_READ_CD in ('5','7')     then 'card_read_meth_chip_cnt '
   when  CARD_READ_CD in ('1','2','3','4','8') or  CARD_READ_CD is null    then 'card_read_meth_nonchip_cnt'         
   when CARD_READ_CD in ('0')   then 'card_read_meth_notpresent_cnt'
    else 'Unknown' End  as CARD_READ_METH
from fact201711      EE

group by 1,2
)
 with data primary index (card_num )
ON COMMIT PRESERVE ROWS;


sel   sum( CARD_READ_CD )  where CARD_READ_CDin ('1','2','3','4','8')  or CARD_READ_CD is null
 from EE

iam not getting exact results .. null values are missing i think.. in above sql they are converting into 1 and adding up
but in below sql is not adding up . i think my logic is wrong pleas can i give me the logic

7 REPLIES
Senior Apprentice

Re: syntax help

Hi,

You're correct, the NULL values are ignored for the SUM function.

 

To avoid that use COALESCE.

sel   sum( coalesce(CARD_READ_CD,1) )  
from EE
where CARD_READ_CDin ('1','2','3','4','8')  or CARD_READ_CD is null

But I don't think that is the cause of your differing results, you're adding up different things. This sql is adding up the values of CARD_READ_CD whereas the earlier code was adding up a value of READCNT.

You probably need to replicate your original CASE logic (below);

sum(case when (ee.CARD_READ_CD in ('1','2','3','4','8')  or ee.CARD_READ_CD is null)   then 1 else 0 end)

HTH,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: syntax help

its working thanks dave

Enthusiast

Re: syntax help

Dave ,

same issue with null values ...

 

if i write query like below mentioned i am getting correct numbers

 

select pref_cd, count(Pref_Cd_Id)  from 
(
select
t5.card_num

,Pref_cd
 ,Case when P.Pref_cd ='ENG' then  1
            WHEN P.Pref_cd ='SPN' then  2 
            when P.Pref_cd is null  then  3 
            else 0 End   as Pref_Cd_Id
 FROM  fact201711                       T5    left join
                 acctdph   Fact 
                 on T5.card_num = Fact.card_num                                left  join  
    (SELECT  distinct(cust_num), type_cd, stypekjd_cd, Prefvkg_cd, MAX(a.obsv_eff_dt) as obsv_eff_dt
                    FROM  TMPRSVA A
                   
                       GROUP BY 1,2,3,4) P
       on FACT.cust_crdhd_num = p.cust_num
       WHERE Fact.date= 201711
group by 1,2,3,4,5,6,7,8) A
group by 1

 

results

1 SPN   2,888,665
2 ENG   10,104,935
3 ?        37,327,424

 

if i create temp table like below null numbers are missing

 

create VOLATILE  table lang as
(

select
t5.card_num

,Pref_cd
 ,Case when P.Pref_cd ='ENG' then  1
            WHEN P.Pref_cd ='SPN' then  2 
            when P.Pref_cd is null  then  3 
            else 0 End   as Pref_Cd_Id
 FROM  fact201711                       T5    left join
                 acctdph   Fact 
                 on T5.card_num = Fact.card_num                                left  join  
    (SELECT  distinct(cust_num), type_cd, stypekjd_cd, Prefvkg_cd, MAX(a.obsv_eff_dt) as obsv_eff_dt
                    FROM  TMPRSVA A
                   
                       GROUP BY 1,2,3,4) P
       on FACT.cust_crdhd_num = p.cust_num
       WHERE Fact.date= 201711
group by 1,2,3,4,5,6,7,8)  with data primary index (card_num
)
ON COMMIT PRESERVE ROWS;

 

results:

 

 PREF_CD Count(PREF_CD)
1 SPN   2,888,665
2 ENG   10,104,935
3 ?              0       ---------- numbers are missing here  need to use any coalease function ?

 


sel Pref_cd, Pref_Cd_Id from lang where Pref_cd is null  
i can see pred_cd_id as 3  for null values

 

 

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

 

 

please let me know

Teradata Employee

Re: syntax help

In the last instance you are doing COUNT(PREF_CD) but in the prior example it's COUNT(PREF_CD_ID).

COUNT does not count NULL values.

 

You may also need to CREATE MULTISET VOLATILE TABLE to retain any rows that are entirely duplicate.

Enthusiast

Re: syntax help

Its typo..

 

But  in first sql  its giving count of null values i am assigning  nulls values to 3.  

Highlighted
Junior Contributor

Re: syntax help

If you want to count nulls why don't you simply use COUNT(*)instead?

Enthusiast

Re: syntax help

Here difference is temp and upper select statemenet difference.

 

in temp table i am sending the values and on that  table i am pulling numbers where nulls are not coming.

 

if i create normal upper select statemennt nulls ar counting up...