I have two tables in teradata
if matcing value found col should join on matching value else nearest lower value.
Please help me
I have to implement this scenario,,,It's urgent
Thanks in advance
Writing the join-condition isn't that hard, but performance will be horrible :-)
What's your Teradata release and the number of rows in those tables?
Are those columns unique?
I am using teradata 14
I am having arround 2005527 rows in a table
I have to apply this condition in ON keyword
in other table I have 1916 record and I have to join this table to first one table.
I have applied other conditions as well in ON cluase like t1.id = t2.id
But I have to apply nearest matching condition as well on another column
the join is like this:
select t2.*, t1.*
from table2 as t2
join table1 as t1
on t1.i = (select max(t3.i) from table1 as t3 where t3.i <=t2.i)
But this result in a product join, which might be ok if you got another joiin condition.
I prefer following approach:
UNION both columns, find the last value using an OLAP function and then join back to both tables:
over (order by coalesce(v1,v2), v2
rows unbounded preceding) as newV1
select i as v1, null as v2 from table1
select null as v1, i as v2 from table2
) as dt
qualify v2 is not null
Put this in a Derived Table and join back:
select t1.*, t2.*
from table1 as t1 join (previous_query) as dt
on t1.i = dt.newv1
join table2 as t2
on dt.v2 = t2.i
Depending on the actual data this might be much more efficient...