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 all,

please can any one help on syntax.

 

I have two temp tables .. need join the them in final table  i wrote query but its giving wrong results .

here the syntax:

 

create VOLATILE  table Method_A as
 (  SELECT all_num,
 CAST(all_READ_METH_CD  as int) AS  all_READ_METH_CD                                                                              
 from test      A
group by 1,2
)
 with data primary index (all_num )
ON COMMIT PRESERVE ROWS;

 

create VOLATILE  table Method_B as
( SELECT ALL_num,
CAST(ALL_READ_METH_CD  as int) AS ALL_READ_METH_CD
from test2  B
 group by 1,2
 )
 with data primary index (ALL_num )
ON COMMIT PRESERVE ROWS;

 

create VOLATILE  table Method_All as
( Select  B.ALL_num ,
(A.ALL_READ_METH_CD  ) + (B.ALL_READ_METH_CD) AS ALL_READ_METH_CD
From     Method_A   A  left  outer Join
    Method_B B
 ON B.ALL_num  = A.ALL_num
group by 1,2
 )
 with data primary index (
 ALL_NUM
)
ON COMMIT PRESERVE ROWS;

 

need to get ALL_READ_METH_CD data from two tables.. its not a aggregate value.

data for ALL_READ_METH_CD   column is 2,3,5,1,6 numbers...

 

union function is not working for me . so created temp tables

thanks  

6 REPLIES
Senior Apprentice

Re: syntax help

Hi,

It would be a lot easier for people to help you if you could give some sample input and output data values - i.e. given certain data values in tables Method_A and Method_B, what should the final result be?

 

Your post says "need to get ALL_READ_METH_CD data from two tables".

- Do you want unique values of column ALL_READ_METH_CD? (A)

- If two rows have the same value for column ALL_READ_METH_CD do you want to see that value twice? (B)

 

For answer 'A' above you could code:

SELECT ALL_READ_METH_CD
FROM Method_A
UNION
SELECT ALL_READ_METH_CD
FROM Method_B;

For answer 'B' above you could code:

SELECT ALL_READ_METH_CD
FROM Method_A
UNION ALL
SELECT ALL_READ_METH_CD
FROM Method_B;

As I said above, it really depends on what you want as an answer.

 

Assuming that the above possibilities do not provide what you want, please provide some sample input and output data (show a number of data rows) and then we can probably help you.

 

Cheers,

Dave

 

 

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

Re: syntax help

Ok for  eaxmple :

 

SELECT ALL_READ_METH_CD,count(*)
FROM Method_B where ALL_READ_METH_CD in (1)   ;

out put  is

ALL_READ_METH_CDCount(*)
1183,982,467

 

SELECT ALL_READ_METH_CD,count(*)
FROM Method_A where ALL_READ_METH_CD in (1) 

ALL_READ_METH_CDCount(*)
1

0

 

i ran this sql

SELECT cardnum,  ALL_READ_METH_CD
FROM Method_A where ALL_READ_METH_CD in (1)
UNION
SELECT cardnum ,ALL_READ_METH_CD
FROM Method_B where ALL_READ_METH_CD in (1);

 

cardnum is unique here  , i am getting less rows around 20910384 iam expecting  183,982,467

hope it is clear please let me know if u have any questions

 

Senior Apprentice

Re: syntax help

Hi,

 

You say "cardnum is unique here  , i am getting less rows around 20910384 iam expecting  183,982,467".

 

In this set of data I don't think that 'cardnum' is unique. The UNION in your sql tells the dbms to combine the two answer sets and then remove any duplicate rows. In your case the 'row' consists of columns "cardnum" and  "ALL_READ_METH_CD" so it looks like you have multiple rows in 'Method_B' for a particular cardnum value where 'all_read_meth_cd' = 1.

 

You could confirm this by running the following sql:

SELECT count(distinct(cardnum))
FROM Method_B
WHERE all_read_meth_cd = 1;

I would expect the returned value to be: 20,910,384

 

To get your full ~183M rows returned, change your sql from 'UNION' to 'UNION ALL'. The 'ALL' option tells the dbms to return duplicate rows.

 

Let me know what happens.

 

Cheers,

Dave

 

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

Re: syntax help

I am getting this many rows when i ran ur sql

2,328,958

Senior Apprentice

Re: syntax help

Hi,

So that confirms that cardnum is not unique in your data. Out of ~183m rows you only have ~2.3M values of cardnum.

 

Did you try using UNION ALL as I suggested above?

If you did, did that give you the correct answer?

 

Cheers,

Dave

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

Re: syntax help

ok.it is working with union all thanks.. changed some syntax..

 

another question :

how to bring null values as sum .

here goes ex:

 

i have sql

 SELECT card_num,
   
    sum(case when (ee.CARD_CD in ('1','2','3','4','8')  or ee.CARD_CD is null)   then 1 else 0 end) as readcnt
from fact201711 ee
group by 1

 

data is -140k all null values summing to 140k

 

i wrote diff sql buts its not working anthing wrong in logic

 

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

 

 select sum(CARD_CD )  from fact201711 where CARD_CD in ('1','2','3','4','8')  OR CARD_CD IS NULL

 

here null values are converting into 1 ...and there are summing up

in below sql i need that logic ...since i am converting columns to rows...