Comparing values from two tables in a nested where clause.

oracle-11g-r2

This is a rather specific question. I'm working to create row restrictions in Business Objects and the customer wants records to fall within a certain range from another table. The problem is, you can only select one table. A work around I found was that I can select other tables in the WHERE clause.

For Example:

a.RE_UID = b.RE_UID

I have to use:

a.RE_UID IN 
      (select A.RE_UID
       from 
        TABLE1 A 
        join 
        TABLE2 B
        ON 
        A.RE_UID = B.RE_UID
      )

Where I'm stumped is that I need compare off another table with a BETWEEN. Something like:

A.CL_FROM_SVC_DTE BETWEEN B.EFF_DTE AND B.END_DTE

If I just try like I did above I get errors that it expected another character after WHERE:

SELECT * 
FROM 
  TABLE2 
WHERE IN 
(select A.CL_FROM_SVC_DTE 
FROM TABLE1 A 
JOIN TABLE2 B ON A.RE_UID = B.RE_UID
) 
BETWEEN B.EFF_DTE AND B.END_DTE

Best Answer

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.