A stored Procedure with multiple joins-how to replace in 2005 with outer joins? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

A stored Procedure with multiple joins-how to replace in 2005 with outer joins?

Hi,
There is a stored Procedure in SQL 2000.How to use outer joins and replace it in 2005?Please help me out to solve this………..SELECT @as_user_id, c.client_code, count_prior_month = count(wa_a.servicee_id), count_this_month = count(wa_b.servicee_id)FROM ms_a_client c, MS_DB_WORKSERV..MS_W_SPECIFIC_ACTION wa_a, MS_DB_WORKSERV..MS_W_SPECIFIC_ACTION wa_b, MS_DB_WORKSERV..MS_W_RPT_PERSON pWHERE c.status = ‘A’ and p.user_id = @as_user_id and wa_a.user_id = @as_user_id and wa_a.audit_create_date >= @adt_prior_date1 and wa_a.audit_create_date < @adt_prior_date2 and c.client_code *= wa_a.CLIENT_CODE and wa_a.CLIENT_CODE =* p.CLIENT_CODE and wa_a.SERVICEE_ID =* p.SERVICEE_ID and wa_b.user_id = @as_user_id and wa_b.audit_create_date >= @adt_cur_date1 and wa_b.audit_create_date < @adt_cur_date2 and c.client_code *= wa_b.CLIENT_CODE and wa_b.CLIENT_CODE =* p.CLIENT_CODE and wa_b.SERVICEE_ID =* p.SERVICEE_ID
group by c.CLIENT_CODEThis is the condition where i have to replace the *= and =* with outer joins.Anybody please solve this.
Come on! This is not that difficult. A little bit googling and you know what to do. [:)]

First instance I thought you gave him solution, as referred there is a section in BOL in thsi regard.

Actually when i tried to execute the procedure after doing modifications,it shows some error like
"The correlation name has the same exposed name."
Because the wa_a &wa_b tables are used twice for joins.
I am new to sql.Please could you help me
I need the solution for this.


The error means the column multiple instances within the same (query?) context, and that these
instances should be explicitly identified using aliases..

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |