Adding more tables to sql join

Technical support

Moderators: Bob Cergol, Data Access, Cintac

Adding more tables to sql join

Postby cjbates » Thu Dec 06, 2012 7:51 pm

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
cjbates
 
Posts: 25
Joined: Thu May 10, 2012 7:46 pm

Re: Adding more tables to sql join

Postby Bob Cergol » Fri Dec 07, 2012 7:23 pm

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
User avatar
Bob Cergol
Certified Dynamic AI Professional
 
Posts: 359
Joined: Fri Dec 11, 2009 5:48 pm


Return to Technical Support

cron