Chris - 3/29/2001 10:02:49 AM

I am using datashaping to pull information from a MS SQL server database.



I am pulling four chunks of data from four different tables. The web application will allow the user to view a list of law practices
alphabetically, the firms that participate in those practices (again, alphabetically), the contacts for the firms, and the cities in
which the firms practice.



I have the practices select as the parent (because I want to sort by them first), firms as the child, and contacts and cities as
children to firms. Everything is related on FIRM_ID. The problem is that everything displays alphabetically _except_ the name of the
firm. I ran the select for the firm through an analyzer, and it ordered correctly when taken by itself.



If I remove the practices select and make the firm the parent select, it orders alphabetically. In my select, I do not tell the data
to order by FIRM_ID at any point, so why would it insist on ordering by FIRM_ID?



Here's my code:

strSQL = " shape { select P.NM PRA, PRACTICE.FIRM_ID  " & _

" from FIRM_CODE P, " & _

" PRACTICE " & _

" where (PRACTICE.PRAC_CD = P.CD_ID) " & _

  PracString & _

" order by PRA } " & _

" append ( " & _

" ( " & _

" shape { select 	FIRM_NM F_NM, FIRM_ID F_ID " & _

" from FIRM " & _

" where STATE_ID = " & strRegion & _

" order by F_NM} as Firm " & _

" append " & _

" ({ select CT.NM CTY, ADDL_CITY.FIRM_ID " & _

" from FIRM_CODE CT, ADDL_CITY " & _

" where (ADDL_CITY.CTY_ID = CT.CD_ID) " & _

" order by CTY} as City " & _

" relate F_ID to FIRM_ID ), " & _

" ({ select CNTC_NM, FIRM_ID " & _

" from FIRM_CNTC } as CNTC " & _

" relate F_ID to FIRM_ID ) " & _

" ) " & _

" relate FIRM_ID to F_ID ) "



The PracString in the SQL is a series of "AND"/"OR" clauses determined by which practice types the user is searching for.



Any help would be appreciated.



Chris