Sort on field name when joining 2 tables

I know how to join 2 tables. My joindef and erddef are done in ServiceCenter.



The problem I have is to sort data on a specific field name. Here is my example:



QUERY #1



SELECT

probsummarym1.number, probsummarym1.category, probsummarym1.open.time, probsummarym1.company, probsummarym1.product.type, probsummarym1.user.priority, probsummarym1.last.activity, activitym1.operator, activitym1.description

FROM

probsummarym1 ,

activitym1

WHERE

probsummarym1.number = activitym1.number AND

ORDER BY

probsummarym1.company ASC



When I run this query, data are not displayed correctly. They are ordered by probsummarym1.number instead of probsummarym1.compagny like I specified.



QUERY #2



SELECT

probsummarym1.number, probsummarym1.category, probsummarym1.open.time, probsummarym1.company, probsummarym1.product.type, probsummarym1.user.priority, probsummarym1.last.activity

FROM

probsummarym1

ORDER BY

probsummarym1.company ASC



When I run this query, data are displayed correctly ordered by probsummarym1.company.



Why this happen? Is it because in query #1 there is a join between two files? (My joindef and erddef are done in ServiceCenter)



That's strange!! May be it's a bug in the ODBC driver!! ???

[tommy]

I am not sure if I have the answer for you, but could you specify how the erddef and joindefs has been defined.



Also please tell us which version of Crystal Report you are running. And maybe the version of the ODBC driver, on the GPUG mailing list I have seen several messages about errors in the ODBC driver especially with SC4.

Definition of my joindef:



probsummary-activity

probsummary

activity



Definition of my erddef:



File 1: probsummary

File 2: activity

Field name of File 1: number

Field name of File 2: number

Relation type: one to many



I'm using ServiceCenter 4.0.7 and Crystal Report 8.0



I tried that with ServiceCenter ODBC driver 4.0.0, 4.0.5 and 4.0.7. The 4.0.0 driver is less buggy than 4.0.5 or 4.0.7.



Thanks

Am I the only one who have that problem (sorting data by field name)?

[mmrobson]

I had a similar problem when working on a report where I had two separate groups, one for assignee and one for the incident (problem) ticket number. The assignee name group was the direct parent to the ticket number group and both where using ascending sort order. The report was ordering first on the ticket number and then the assignee name.



Since the order the incident ticket numbers came in was not important, I just set the sort order of the ticket number group to use its original order and everything worked just fine. I never was able to find a true solution to the problem, however.

I have a problem ticket opened at Peregrin tech support for that problem.



The ServiceCenter ODBC driver seems to be really buggy. I presently use ODBC 4.00.00 (4.00.50 and 4.00.70 are too buggy). >=(