varchar/nvarchar issue?

rbuser

System spec:
RB Ent v10.04
Delphi 6.02
SQL Server 2000 SP3
ODBCExpress (6.02) DADE plug-in

We have a reasonably simple list report that is sequenced by a Surname
field. One of the surnames is "Al-Zoubi" and the surname field data type was
upsized from an old Access database to nvarchar(20).

A recent series of database modifications has converted this field from
nvarchar to varchar, in keeping with the rest of the varchar fields in the
database.

Now, when running the report, none of the other employees whose surname
starts "Al" (e.g. Alcock, Allen) appear in the report, but their figures are
all added up and totalled under the record for Al-Zoubi. I can get the
report to work again one of two ways:

1. Changing the field type back to nvarchar. This works but is not a
long-term option for us.
2. Changing the surname of Al-Zoubi to "AlZoubi" or "Al Zoubi" (i.e.
replacing the hyphen with nothing or another character such as a space).

Is there a known issue with hyphenated varchar characters in RB? The effect
is almost as if all "Al" employees are being grouped under the first one,
Al-Zoubi.

It might be of further interest to note when executing an SQL query, if the
field type is varchar, Al-Zoubi appears first in the list of "Al" surnames.
If it is an nvarchar character, he appears at the end of the "Al" surname
people.

Jason Sweby
Software Development Manager,
Carval Computing Limited, Plymouth, UK

nicocizik

Hi Jason,

In my quick testing with SQL Server 2000 and ADO I was unable to recreate
the issue you described below. If possible, please let me know more
information about your data and query so I can possibly recreate the issue
here. Perhaps some steps I can take would be helpful as well.

--
Regards,

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

rbuser

Hi
Is it possible to change the "BreakName" of the group band to a unique
identifier such as
ID etc. Grouping by a varchar / nvarchar should be avoided unless you know
the data is unique.
Dominic

rbuser

Dominic

Thanks for the suggestion. However, the one group on the report is on a
field called Clock_number which is an Integer so this isn't the issue. The
grouping by surname is the anomaly which I am posting about, there is no
such group either on the query or the report design, and it only happens
with this guy whose surname contains a hyphen. Remove the hyphen and the
report goes back to normal.

And it prints the other 200+ people just fine too.

Jason.

rbuser

Hi Nico,

Thanks for responding. The report is fairly simple but it has a DADE query
with 3 linked queries. I could send it to you but of course without the
database to go with you wouldn't be able to view the query. And the database
is LARGE!

Let me try and recreate it on a smaller scale and then I'll come back to
you.

Thanks,
Jason.

rbuser

I have finally been able to make time to recreate the issue with a smaller
database and a simple report. The Delphi 6 project files and the SQL Server
2000 database backup file have been emailed to support. For the record, I
have since upgraded to RB Ent 10.06.

In the report, there are 2 linked DADE pipelines. There is a person on the
report with a surname "Al-Zoubi". If the hyphen is present, the other
employees whose name begins with "Al" disappear from the report and their
figures appear under the detail line for Al-Zoubi. If the hyphen in his name
is replaced with a space then the report works correctly.

It has been set up without using ODBCExpress as it was assumed support
wouldn't have this, therefore it has been set up using the BDE through an
ODBC link. The alias is called Test although you can obviously change all of
this seeing as you have the source from me.

Thank you for your time,
Jason.

nicocizik

Hi Jason,

Thanks, I will take a look at your example and get back to you via. email as
soon as possible.

--
Regards,

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