Dynamic Join on the RPD?

Question

I have two tables and the join between both needs to be defined by an ID and "between" clause.

I have a a.HISTORY_EFFECTIVE_DATE and a a.HISTORY_EXPIRATION_DATE on table A. And a b.DATE_COLUMN in table B. So my Join will be:

select a.col1, a.col2, b.date_column

from table1 a inner join table2 b

on

a.id = b.id

and

b.date_column between a.h_eff_dt and a.h_exp_dt

How can I translate this into the RPD? I tried and I got an error saying that it was an invalid expression.

Gianni Ceresa · Answer

Christian Berg wrote:+1 it works upon join creation but will throw that error if you try to modify an existing join

If the existing is a simple join with just =, > and < operators as Christian said you get an error. This happen because when writing the BETWEEN it's not a "simple" join but switch to a "complex" join. So if already existing join with just ID = ID delete it and create a new one.

[Deleted User] · Answer

+1 it works upon join creation but will throw that error if you try to modify an existing join

Gianni Ceresa · Answer

Hi,

Try again as it works and that's the way to make it ...

When you draw your physical join you can't write directly into the "Expression" field, you must click the icon on the right to open the "Expression Builder" and there you can write your full condition (id = id AND date between date and date).