Comparing values from two tables in a nested where clause.
If you are selecting everything from Table2 with the condition on Table1, what you could do is this:

SELECT T2.* FROM TABLE2 T2
INNER JOIN TABLE1 T1 ON T2.RE_UID = T1.RE_UID
WHERE T1.CL_FROM_SVC_DTE BETWEEN T2.EFF_DTE AND T2.END_DTE


I hope it helps.