Alias' Problems 1

[d222222]

I have several fields in multiple tables that have the same name so I have to use alias's. I have changed the code so many times and keep getting errors either from the alias's, the grouping or the key fields.

This is the latest code:

SELECT rmpctsum.bl_id, rmpctsum.dv_id, bl.bl_id, bl.bl_id_old, bl.name AS bldg_name, bl.address1, bl.own_or_ls, bl.campus, bl.area_usable, bl.area_chargable, bl.fmw_active, bl.city_id, city.city_id AS city_city_id, city.name AS city_name, dv.dv_id AS dv_dv_id, dv.name AS dept_name

FROM rmpctsum, bl, city, dv

WHERE rmpctsum.bl_id IS NOT NULL AND rmpctsum.dv_id IS NOT NULL AND rmpctsum.bl_id = bl.bl_id AND bl.city_id = city.city_id AND rmpctsum.dv_id = dv.dv_id AND bl.fmw_active = 1

GROUP BY rmpctsum.bl_id, rmpctsum.dv_id, bl.bl_id, bl.bl_id_old, bl.name, bl.address1, bl.own_or_ls, bl.campus, bl.area_usable, bl.area_chargable, bl.fmw_active, bl.city_id, city.city_id, city.name, dv.dv_id, dv.name

The error message says "The column bl_id was specified multiple times for rmpctsum." This is the message I got after adding "bl.bl_id" to the select statement because the message before that was "Multi-part identified bl.bl_id could not be bound in this code" so I thought it needed the bl.bl_id to bind the rmpctsum.bl_id and bl.bl_id together and bind the 2 tables.

I can't figure out what I am doing wrong since this works in SQL 2005 but not on the website.

 

[markros]

Can you put this in stored procedure? BTW, if you're in the position to change the database design I would advise you to change it as soon as possible to create better names. Otherwise it would be a maintenance nightmare working with such names.

 

[d222222]

Unfortunately I can't do either one because it is being done in an existing application that already had the tables created. Making changes to the table would be a nightmare. I agree that it would be a lot better if the names were better but I don't have any choice in the matter. We also can't use stored procedures.

Any ideas on how to get around this?

 

[markros]

If you place here table creation scripts, I can play around on Saturday's night. But you're saying that it's working in SSMS and not working in the application? What is your front-end?

 

[d222222]

We are using Archibus Web Central.

I got it working. I don't know if it was the SQL statement changes or the changes to the other code but all of a sudden it works.

Thank you for your efforts.

 

[markros]

Ok, great that it worked. All the best to you.