IDT Join using BETWEEN for date fields

[former_member223074]

Tool: Information Design Tool, BO4.0 SP5 P6

Hi,

I have 2 tables (SQL server) which I want to join using a join criterion that involves BETWEEN. One of them is a derived table and the field used is a date field stored as VARCHAR - YYYYMMDD. I tried all approaches like converting both of them into numeric and date formats and use a join criterion like

table1.date BETWEEN table2.fromdate AND table2.todate. But I am seeing multiple rows instead of one matching record which I should be seeing here.

(Cartesian product or Trap?) But something like table1.date = table2.fromdate works fine. And table1.date <= table2.fromdate AND table1.date >= table2.todate also results similar to BETWEEN. Any thoughts for this issue please?

Thanks,

Anil

[Former Member]

Hi Anil,

Have you set the cardinalities correctly? It might be the issue.

When you run the same query against database directly, do you get desired results?

Regards,

Yuvraj

[former_member223074]

Yuvraj,

Thanks for the response. I have got this resolved now. Looks like the derived table which I generated had caused the problem. Corrected the data there with right range of values populated in the derived table which took care of the issue.

[former_member4998]

Hi

If possible can you please provide the SQL code...for better understanding