Distinct command in SQL being added

rbuser

RB12, SQL Server database, D7

Certain reports that worked fine in previous versions are generating an
error in RB12. In investigating I found that on query joins, the Magic SQL
is automatically adding Select Distinct which causes an error if the query
includes a field type of text or ntext (memo fields).

Thanks,
Bob

rbuser

I think I found the place in source code to fix the problem of Distinct
being added. However, I would like to confirm that the change below is
correct to solve the problem and will not cause other problems.

Commented out line that reads "Distinct := True" and changed to False in
daMagicSQL.pas function shown below:

function TdaMagicSQL.GenerateMagicSQL: Boolean;
begin

Init(FRootSQL);

GetSQLInfo(FRootSQL, nil);

if (MasterSQL <> nil) then
FMagicLinks.ValidateMasterSQLFieldNames;

if (GroupByFieldCount > 0) and not(HasAggregates)then
begin
{remove all group by fields and add Distinct}
ClearGroupByFields;
Distinct :=False;
//Distinct := True;
end;

LinkingSQL := True;

try
FMagicSQLText.Assign(SQLText);

finally
LinkingSQL := False;
end;

Result := True;

end; {function, GenerateMagicSQL}

nardmoseley

Removing that code may result in the Magic SQL returning duplicate rows and
thus producing incorrect output.

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

nardmoseley

This issue in the queue to be researched.


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

rbuser

Please understand that this problem which is a different behavior from RB11,
renders reports non-functioning. Every report with joins that include memo
fields such as text or ntext will generate an error and return no records.

The problem of significant urgency to me in that I already have customers
with the new version and having problems.

Thanks,
Bob

nardmoseley

Please create a simple example project using standard Delphi components and
ReportBuilder. If possible use the SQL Server Pubs or Northwinds sample
databases. Or provide steps to create sample table(s). Send in zip format to
support@ and we can research it. First step to resolving any issue is
recreating it here.

As a temporary work around, try double-clicking the visual link to access
the Link dialog. Select the SQL tab and then choose the Parameterized SQL
option. The generated linking SQL displayed at the bottom of the dialog
will change, review it and then test the report.



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

rbuser

I have tried the option of changing the sql to Parameterized and that does
work. However, that is not a practical option with a thousand customers all
with self-designed or customized reports.

Also, the code change I mentioned before seems to work and generates the SQL
just like RB 11 did. The user can always select to make a query Distinct if
desired. Distinct is not applicable when memo / text fields exist.

It seems very clear to me. I have screen shots as well. When you use
joined tables and one has memo fields, the MagicSQL adds Distinct to the
query causing the previous stated SQL error. However, I will be glad create
a small database and a report to provide an example.

So where do I send this example? You have already told me not to email you.

Thanks,
Bob

rbuser

Sorry, I see where you said to send the sample.

Bob

rbuser

Is there any resolution on this yet?

It seems to me the thing to do is make it function as it did before which
was to not add the distinct key word. The user can always use it in the
query if desired.

Thanks,
Bob

nardmoseley

RB 12.01 contains a fix for this issue. I had already emailed Bob, but am
posting here for anyone else following this thread.


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