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.
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