TSQLBuilder changes between 11.02 and 11.07

rbuser

Hello,

I recently moved updated from 11.02 to 11.07, and something about how the
SQLBuilder is handled is done differently. For one of our reports, I have a
table included on the data tab ('RptProduction'). All fields are included,
and there is no sorting or grouping. At run time, the user is prompted to
select an option which determines how I modify the table properties. This
is done with the following (in RAP).

SQLBuilder := TdaSQLBuilder.Create(Report.Datapipeline);
SQLBuilder.SelectTables.Add('RptProduction');
SQLBuilder.SelectFields.AddAllFields;
if (DataType = 1) then begin
SQLBuilder.SearchCriteria.Add('RptProduction', 'StartDate', '>=',
DateToStr(Sunday));
SQLBuilder.SearchCriteria.Add('RptProduction', 'StartDate', '<=',
DateToStr(Saturday));
end else begin
SQLBuilder.SearchCriteria.Add('RptProduction', 'ShipDate', '>=',
DateToStr(Sunday));
SQLBuilder.SearchCriteria.Add('RptProduction', 'ShipDate', '<=',
DateToStr(Saturday));
end;
// The following line is only used in this example
ShowMessage(SQLBuilder.SQL.SQLText.Text);
SQLBuilder.ApplyUpdates;
SQLBuilder.Free;

This would work fine with 11.02, and the text shown in the ShowMessage is
the SQL I want to use:

SELECT RptProduction.Serial,
RptProduction.StartDate,
RptProduction.ShipDate
FROM RptProduction RptProduction
WHERE ( RptProduction.StartDate >= '2010-01-17')
AND (RptProduction.StartDate <= '2010-01-23')


With 11.07 the SQL ends up like:

SELECT RptProduction.Serial,
RptProduction.StartDate,
RptProduction.ShipDate,
RptProduction.Serial Serial_2,
RptProduction.StartDate StartDate_2,
RptProduction.ShipDate ShipDate_2
FROM RptProduction RptProduction,
RptProduction RptProduction_2
WHERE AND ( RptProduction.StartDate >= '2010-01-17')
AND (RptProduction.StartDate <= '2010-01-23')
)

which then throws an error. However, if I remove the two lines to add the
table and add all fields, it works okay. Was there previously a check to
see if an added table name was equal to an already present table?

Thank you,
-Steve

nardmoseley

There was a bug fix to allow self-joins.

You should be calling SQLBuilder.Clear if you want to clear the existing SQL
definition.

SQLBuilder := TdaSQLBuilder.Create(Report.Datapipeline);

SQLBuilder.Clear; // clear SQL definition

// add tables, fields, etc.



--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com