teradata sql help

Database

teradata sql help

there are two tables table1 and table2

table1 ---columns

C1 c2 c3 c4 c5

where c3 and c4 are datebegin and dateend

table2----columns

d1 d2 c5

where d1 and d2 are dategin and date end

now i need to join table1 and table2 based on c5

and update c1 to 'Y' if the date begin and datened of table1 lies with in the datebegin and dateend of table2

and c2 coloum in table1 is 'Y'

update c1 to 'N' of the if the date begin and datened of table1 lies with in the datebegin and dateend of table2

and c2 column in table1 is 'N'

for all other conditions c1 is null
Tags (3)
3 REPLIES
Enthusiast

Re: teradata sql help

Try this:

Create Volatile Table Table1
(C1 Char ,
c2 Char,
c3 Date Not Null,
c4 Date Not Null,
c5 Smallint Not Null)
Unique Primary Index (c5)
On Commit Preserve Rows
;

Create Volatile Table Table2
(d1 Date Not Null
, d2 Date Not Null
, c5 Smallint Not Null
) Unique Primary Index (c5)
On Commit Preserve Rows
;

Insert Into Table1 Values (Null,'X',Date - 5, Date -3,1);
Insert Into Table1 Values (Null,'Y',Date - 5, Date -3,2);
Insert Into Table1 Values (Null,'N',Date - 5, Date -3,3);
Insert Into Table1 Values (Null,'Y',Date - 5, Date -3,4);
Insert Into Table1 Values (Null,'N',Date - 5, Date -3,5);

Insert Into Table2 Values (Date-6,Date-4,1);
Insert Into Table2 Values (Date-6,Date-4,2);
Insert Into Table2 Values (Date-6,Date-4,3);
Insert Into Table2 Values (Date-8,Date-6,4);
Insert Into Table2 Values (Date-8,Date-6,5);

Select * From Table1
Order By 5
;
Select * From Table2
Order By 3
;

Select Table1.*
From Table1
Join Table2
On Table1.c5 = Table2.c5
And Table1.c2 in ('Y','N')
And (Table1.c3,Table1.c4)
Overlaps (Table2.d1,Table2.d2)
Order By 5
;

Update Table1
Set c1=c2
Where Table1.c5 = Table2.c5
And Table1.c2 in ('Y','N')
And (Table1.c3,Table1.c4)
Overlaps (Table2.d1,Table2.d2)
;

------------------------------------------------------
You will find overlaps in Chapter 10 of SQL Reference: Functions And Operators.

Re: teradata sql help

This works as well...

update t1
from
table1 t1,
(
sel
c1,
c2,
c3,
c4,
t1.c5,
d1,
d2,
case
when c3 > d1 and c4 < d2 and c2 = 'Y' then 'Y'
when c3 > d1 and c4 < d2 and c2 = 'N' then 'N'
else null
end as formatted_c1

from
table1 t1
inner join
table2 t2
on
t1.c5 = t2.c5
) ab
set c1 = ab.formatted_c1
where
t1.c5 = ab.c5
Enthusiast

Re: teradata sql help

The two answers do not give the same results; it depends on your definition of overlapping dates.
Consider a case where table1 has dates of 2010-03-14 and 2010-04-15; table2 has 2010-03-16 and 2010-04-16. In this case, my answer returns N or Y - the dates overlap. MewithTeradata's answer returns null.
Which answer you want depends on whether the dates in table2 must lie completely within the dates in table1.