0

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
2
  • Why can't you perform joins normally?
    – Erik
    Mar 28 '16 at 17:32
  • Provide table definition with some sample data and expected output.
    – atokpas
    Mar 29 '16 at 0:32
0

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.

0

Not sure I understood neither the problem nor the restriction to select from one table alone, so this is an educated guess at best:

SELECT B.* 
FROM 
TABLE2 B
WHERE b.RE_UID IN (
    select a.RE_UID
    FROM TABLE1 A 
    WHERE A.CL_FROM_SVC_DTE BETWEEN B.EFF_DTE 
                                AND B.END_DTE A.RE_UID
);
1
  • Yeah, I don't blame you. Basically, I'm working on creating Restrictions to a Universe in Business Objects. Customer wants certain users to have records with specific dates. In Business Objects when creating Row-Level Restrictions you can only select one table at a time without in joins. Basically, the restrictions work only as WHERE clauses (I think, anyways). So basically when you create a restriction on a table you choose the table and you choose the WHERE clause. If I choose Table1 it will and put in the WHERE clause BOBJ automatically creates Select * from WHERE (whatever I enter)
    – KeithGuest
    Mar 28 '16 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.