Joins in Designer using fields not part of join tables

[Former Member]

Hi,

I have 3 tables:

Sales with fields {Sales_Key,Date_Key,ValidData_Key, TotalSales}

Date with fields {Date_Key, CalendarDate}

ValidData with fields {ValidData_Key,ValidEffDate, ValidEndDate}

I'm trying to create the following join in Designer between the three tables without creating a loop.

SELECT * FROM

Sales S,

Date D,

ValidData V

WHERE

S.Date_Key = D.Date_Key and

S.ValidData_Key = V.ValidData_Key and

D.CalendarDate between V.ValidEffDate and V.ValidEndDate

Essentially, I want to be able to use fields from table(s) other than the two tables involve in the join within the join condition. How can this be achieve?

Thanks,

Jimmy

[former_member142702 · Accepted solution]

Hi,

have you considered using a derived table for this?

Regards,

Stratos

[Former Member · Accepted solution]

I suppose I could create a derive table to grab the actual dates from the Date table over to the Sales Fact. This will allow me to use the actual date in my join between the ValidData table. But I would prefer not to use a derive table. Is there a more direct way to do this?

Thanks,

Jimmy

[thomas_wagenbichler · Accepted solution]

Hello Jimmy,

define your join without the condition "D.CalendarDate between V.ValidEffDate and V.ValidEndDate". In Supervisor, you can create a constraint on the Date table, using this condition.

Regards,

Thomas

[Former Member · Accepted solution]

I'm on XIr2. How would I achieve this?

[Former Member · Accepted solution]

I ended up using a derive table.