All Forums Database
HateOra 17 posts Joined 10/13
20 Nov 2013
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
 
 

ulrich 816 posts Joined 09/09
20 Nov 2013

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.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

HateOra 17 posts Joined 10/13
20 Nov 2013

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
 
 
 

HateOra 17 posts Joined 10/13
20 Nov 2013

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?

ulrich 816 posts Joined 09/09
20 Nov 2013

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.
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.