Need help in understanding Teradata's behaviour

Database
Enthusiast

Need help in understanding Teradata's behaviour

Hi Team,

we  have below query , where we are geting stange results, if i just run Inner Query - A alone, i get lets say 100

records but when i run complete query , i.e. lefter outer join with Inner Query - B many records gets filtered out,

some how i am unable to understand this phenomenon.

Also if i remove ( mac(c) and just make it as C and add it to the group by ) i get complete 100 rows.

please help me out on this.

select

A,

b,

c,

d,

e,

F

(

select

a

b

max(c)

sum(d)

sum(e)

from Table1

group by 1,2) A

left outer join

(

select

a

b

max(F)

from Table2

group by 1,2) B

on

A.a = B.a

and A.b = B.b

4 REPLIES
Supporter

Re: Need help in understanding Teradata's behaviour

you don't have at some point an where on B.x = something?

This would convert the outer to an inner join and would filter out rows.

Enthusiast

Re: Need help in understanding Teradata's behaviour

Sorry i didnt get the point :

another thing which we noticed is : if we make changes as below , we get complete 100 records

select

A,

b,

c,

d,

e,

F

(

select

a

b

max(c)

sum(d)

sum(e)

from Table1

where table1.c = (XXXXXX)              -- Newly added

group by 1,2) A

left outer join

(

select

a

b

max(F)

from Table2

group by 1,2) B

on

A.a = B.a

and A.b = B.b

Enthusiast

Re: Need help in understanding Teradata's behaviour

HI Team,

If we add MAX to one of the column we get different result, which is actually wrong... and when we remove the MAX and add that to group by we get correct results.

any idea why?

Supporter

Re: Need help in understanding Teradata's behaviour

it is very difficult to answer you these questions on your pseudo code - the first query is syntax whise incorrect as columns names are missing. Not a big deal but it means it is manual pseudo code and you could have changed things buy writing it.

You would need to share the whole DDLs and SQL to get good answers.