Join on Between

[Former Member]

Hi All,

I've got an invoice report with data coming from 2 tables dbo.Head and dbo.Positions joined on Head.InvId=Positions.InvId. I now need to add another table dbo.Images that should be joined on Head.Date between Images.DateFrom and Images.DateTo. Problem is that definition of the report is passed through tables and joins made in Links creator, not a command so I can't make such a join in the Links creator. I tried to copy and paste the sql query to command but the report loses fields. Any idea how to cope with it?

Regards,

Przemek

[ido_millet · Accepted solution]

One option is to add the table but remove any joins to it.  Take care of the join logic by adding a record selection condition (Report, Selection Formulas, Record...).

[Former Member · Accepted solution]

I did it with record selection. Images table has and will have only few records so it will do. Thank you Ido.

[ido_millet · Accepted solution]

Excellent. Thanks for closing the loop.

[abhilash_kumar]

Hi Przemek,

Could you elaborate on 'I tried to copy and paste the sql query to command but the report loses fields' please?

You're best bet is to use a SQL Query via the 'Add Command' option for such Joins.

-Abhilash

[Former Member]

Hi Abhilash,

take a look at the attached report and try to add sql command. The new table should be joined on TrN_DataOpe BETWEEN Images.DateFrom AND Images.DateTo.

Regards,

Przemek

Invoice.txt.zip

[abhilash_kumar]

"TrN_DataOpe BETWEEN Images.DateFrom AND Images.DateTo" is the Join between these two tables.

However, you would still need to Join one of these tables to one of the existing tables in the SQL - which table and what fields would that be on?

-Abhilash

[Former Member]

All the joins should look like below:

TraNag

     JOIN TraElem ON TrN_TrNId=TrE_TrNId

     LEFT OUTER JOIN Images ON TrN_DataOpe BETWEEN Images.DateFrom AND Images.DateTo"

[abhilash_kumar]

And what table would this field - TrN_DataOpe be in?

-Abhilash

[Former Member]

In TraNag.

TraNag

     JOIN TraElem ON TraNag.TrN_TrNId=TraElem.TrE_TrNId

     LEFT OUTER JOIN Images ON TraNag.TrN_DataOpe BETWEEN Images.DateFrom AND Images.DateTo"

[abhilash_kumar]

Try creating a new report against the attached SQL and see if all fields appear and work as expected.

If you wish to change the existing report's datasource from tables to this command then it's not going to be an easy task. You're better off creating a new report instead.

-Abhilash

Query.txt.zip

[Former Member]

Thank you Abhilash, Ido's suggestion does the job as much as I need it.