Adding joins

rbuser

I've got stuck at adding join conditions. How do I do that?
TdaTable.AddTableJoin() doesn't do anything for me.

(The meta data can specify predefined joins between tables. I must popup a
dialog where the user can select one of these predefined joins. When a join
is added, I must add the new table and add the predefined join conditions.)

nicocizik

Hi Peter,

The TdaSQLBuilder does make the handling of the TdaSQL object simpler
however it is not by any means "simple". Just about everything you can do
(or would want to do) with the TdaSQL object, you can do with the
TdaSQLBuilder. I would suggest spending some time with it and taking a look
at the code examples located in the help topic. For instance, adding a join
using the SQLBuilder is as simple as adding the following code...

lSQLBuilder := TdaSQLBuilder.Create(Report.DataPipeline);

lSQLBuilder.Clear;

lSQLBuilder.SelectTables.Add('Customer');
lSQLBuilder.SelectTables.AddJoin('Orders', 'Customers', 'CustNo', '=',
'CustNo');
lSQLBuilder.SelectTables.AddAllFields;

lSQLBuilder.ApplyUpdates;

lSQLBuilder.Free;



--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

The reason I'm not using the TdaSQLBuilder is that I have needed to make
some changes to TdaSQL to meet some of the project requirements. Using
TdaSQLBuilder would simply add another level of complexity.

Using the example you provided, I've traced through the code, and all seems
to work nicely, yet... the "new" table is not added.

If I explicity add the "new" table using TdaSQL.SelectTableOnly() (which
works fine) then try adding the join, then I don't get the join condition.

In other words, it's as though I'm successfully adding the join, but the
join is not applied to the TdaSQL I'm using (regardless of whether or not I
use TdaSQLBuilder).

Any ideas on that?

nicocizik

Hi Peter,

In my testing with the code I posted in my previous message, the table join
seemed to work correctly. If you would like to see how the AddTableJoin
routine is used in the TdaSQLBuilder class, take a look at the private
routine TdaSQLSelectTables.AddTableJoin. This is located in the
daSQLBuilder.pas file. You will see that it is very important that the
JoinOperator is properly assigned.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Got it to work like so (in case anyone else has the same problem).

(Note: T...Join is a pretend class meant to make reading the sample code
more legible.)
- Select the new table using TdaSQL.SelectTableOnly() to enable the user to
select the same table multiple times.
- Select the new table *before* creating the Builder so the temporary SQL
within the Builder does not overwrite the fact that we've added a new table.
- Apply the changes made to Builder.SQL back to SQL using
"SQL.Assign(Builder.SQL);".

procedure AddTableJoin(const SQL: TdaSQL; const daTable: TdaTable; const
Join: T...Join);
var
i: integer;
Builder: TdaSQLBuilder;
daJoinTable: TdaTable;
begin
// Get JoinTable
daJoinTable := SQL.SelectTableOnly(...);
// Use TdaSQLBuilder
Builder := TdaSQLBuilder.Create(SQL);
try
// Add joins
for i := 0 to Join.ConditionCount-1 do
Builder.SelectTables.AddJoin(daJoinTable.SQLAlias, daTable.SQLAlias,
Join.JoinTableFields[i], Join.Operators[i], Join.TableFields[i]);
// Apply changes
Builder.ApplyUpdates;
SQL.Assign(Builder.SQL); <- critical step
finally
Builder.Free;
end;
end;

nicocizik

Hi Peter,

Great, thanks for the info and glad you got it working.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com