Playing around with the supplied queries with Query Analyzer on my SQL server, I got the same problem as you with the non-aliased query. However, I also got problems running the aliased query.
In looking for a valid query syntax, I found some references to the fact that in order to be compliant with the SQL-92 standard, MS SQL Server 2000 is more strict on implied joins. E.g. WHERE CSProfileAuthorization.CSAUTHORIZATIONREGISTRYID= CSAuthorizationRegistry.ID I suspect that this is the reason that MS SQL doesn't work and MySQL does. IMHO, you'd have the same issue if you went to Oracle or any DB that applies the standards more strictly. I know I've hit problems like that in moving MySQL development code to MS SQL (especially foreign key contraint enforcement). You might try restructuring the query to use two explicit joins to create a more DB vendor independent format. > -----Original Message----- > From: Kalyani Kale [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 16, 2006 12:59 AM > To: Apache Torque Users List > Subject: RE: Problem with MSSQL and Criteria.addSelectColumn > > > See my comments in blue > > > > > That seems like a very dangerous implied join between the > two tables. > > > > > I pasted just a part of the query to give an idea of what's > happening. The actual query does contain proper joins. > > > > > That said, you might want to dump the specific query > > Yes, I did that. I pasted the output in SQL Server's query > analyzer and it didn't work which is why I modified it. > > > > 1) This is the original version of the query > > > > SELECT > > CSAuthoToGroup.CSAUTHOGROUPREGISTRYID, > > CSAuthorizationRegistry.NAME, > > CSAuthorizationRegistry.TYPE, > > CSAuthorizationRegistry.DESCRIPTION, > > CSAuthorizationRegistry.ID, > > CSAuthorizationRegistry.CSCOMMONSERVICEREGISTRYID > > FROM > > CSProfileAuthorization, CSAuthorizationRegistry > > LEFT JOIN CSAuthoToGroup ON > CSProfileAuthorization.CSAUTHORIZATIONREGISTRYID=CSAuthoToGrou > p.CSAUTHOR > IZATIONREGISTRYID > > WHERE > CSProfileAuthorization.CSAUTHORIZATIONREGISTRYID=CSAuthorizati > onRegistry > .ID > > AND CSProfileAuthorization.CSPROFILEREGISTRYID=5000; > > > > > > 2) The query was later modified to make it work on SQL Server > > > > SELECT > > ag.CSAUTHOGROUPREGISTRYID, > > ar.NAME, > > ar.TYPE, > > ar.DESCRIPTION, > > ar.ID, > > ar.CSCOMMONSERVICEREGISTRYID > > FROM > > CSAuthorizationRegistry ar, CSProfileAuthorization pa > > LEFT JOIN CSAuthoToGroup ag ON > pa.CSAUTHORIZATIONREGISTRYID=ag.CSAUTHORIZATIONREGISTRYID > > WHERE pa.CSAUTHORIZATIONREGISTRYID=ar.ID > > AND pa.CSPROFILEREGISTRYID=5000; > > > > 3) The error I was getting in SQL Server is: > > The column prefix 'CSProfileAuthorization' does not match > with a table name or alias name used in the query. > > > > > > >If the CSAuthoToGroupPeer.CSAUTHOGROUPREGISTRYID is a foreign > > >key field to CSAuthorizationRegistryPeer.ID, then you could > > >also use the much safer CSAuthoToGroupPeer.doSelect(criteria) > > >and then use the CSAuthoToGroupPeer.getCSAuthorizationRegistry() > > >method to get the name information. > > > > The reason, I'm calling BasePeer's doSelect is, I am using > Criteria.addSelectColumn while creating a query which forces > me to call BasePeer.doSelect directly. > > > > Kalyani > > > > > Duke CE Privacy Statement Please be advised that this e-mail and any files transmitted with it are confidential communication or may otherwise be privileged or confidential and are intended solely for the individual or entity to whom they are addressed. If you are not the intended recipient you may not rely on the contents of this email or any attachments, and we ask that you please not read, copy or retransmit this communication, but reply to the sender and destroy the email, its contents, and all copies thereof immediately. Any unauthorized dissemination, distribution or copying of this communication is strictly prohibited. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]