Archives of the TeradataForum

Message Posted: Fri, 21 Nov 2014 @ 09:08:58 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Friday, November 21, 2014 03:41 -->

Hello Forum,

This may be a basic question on joins, but I'm having trouble understanding it.

Need your ideas in understanding join conditions to improve query performance.

Any other inputs in elaborating the scenario or ideas to rewrite this would be highly appreciated.

     ...
     FROM

     DB1_UAM_WORK.TEMP_FACT_FULL_LOAD  Tab1

     INNER JOIN

     DB2_UAM2_RD.DIM_DATE_UTM  Tab2

     ON Tab1.DateValue  BETWEEN Tab2.SValidFrom AND Tab2.SValidTo

     WHERE (Tab1.DateValue <=  (CAST((DATE ) AS DATE)))
     AND ((((TAB1.DateValue BETWEEN TAB2.SCValidFrom AND TAB2.SCValidTO )
     AND (TAB1.DateValue BETWEEN TAB2.PRValidFrom AND TAB2.PRValidTO ))
     AND  (TAB1.DateValue BETWEEN TAB2.PRLValidFrom AND TAB2.PRLValidTO ))
     AND (TAB1.DateValue BETWEEN TAB2.LOCValidFrom AND TAB2.LOCValidTO )))

1. ON Tab1.DateValue BETWEEN Tab2.SValidFrom AND Tab2.SValidTo - Is this a valid join condition? Doesn't it increase the rows, if between is used as they are considering a range of data instead of specific (equals to) condition on both tables?

2. WHERE (Tab1.DateValue <= (CAST((DATE ) AS DATE))) - What is the need to cast Date to date ?

3. How to handle casting in joins/where conditions?

Thank you.

	<Prev	Next>		<<First	<Prev	Next>	Last>>