Adding more tables to sql join

[cjbates]

It does not appear to allow me to join more than 7 tables in the SQL Join tool. Is this a true constraint or is there a way to add tables to the far right?

Thanks
Cynthia

[Bob Cergol]

Cynthia,

The join-builder feature only supports joining 7 tables. (I think this is being expanded in version 7.) Beyond 7 tables you have to use a dyn-view. This is very simple to do and join builder can teach you the SQL. However, if your tables have duplicate column names then you will have to caption them in the view because Dynamic AI doesn't allow ambiguous column names. Note: Join builder will allow you to join any data sources: tables, views, dyn-views.

As useful and as quick-and-easy as join builder is -- I do prefer, and recommend, using dyn-views to build your core data sources on which you will build reports. (I know we've talked about this in the past.) Dyn-views offer advantages such as dynamic replaceable parameters (@Par#@), partitions, etc.

I generally let Dynamic AI write the SQL on a table for me using the "create SQL view" option on the pull down menu on a table. I then cut and paste it into an editor and caption the columns with a prefix that identifies the table, like this simple example:

select
job.id as job_id,
job.cust_id as job_cust_id,
cust.id as cust_id,
cust.name as cust_name
from dbo.[Orders] job
left join dbo.[Customers] cust ON Job.Cust_ID = cust.ID

When you have lots of tables with lots of columns the way the columns show up in report design is extremely convenient because you know what tables everything is coming from at a glance.

I think version 7 will automatically generate these prefixed column captions for you when it creates a SQL view from a table.

Bob