SQL Server Performance

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

Discussion in 'General DBA Questions' started by hindueee, Aug 20, 2007.

  1. hindueee New Member

    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.
  2. FrankKalis Moderator

    Come on! This is not that difficult. A little bit googling and you know what to do. [:)]
  3. satya Moderator

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

    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.
  5. satya Moderator

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

Share This Page