sql storedprocedure problem

Discussion in 'Databases' started by memo2010, Sep 21, 2010.

  1. #1
    please can you help me , this's my first post , i don't know if this the correct place ,
    but i need help .
    i have this code in sql
    SELECT
    Open_and_Closed_Agencies.[U/W Code] AS agt_uw_code,
    Open_and_Closed_Agencies.[Agt Code],
    Dual_Assignment_Table.[U/W Code] AS dual_uw_code,
    case when Dual_Assignment_Table.[U/W Code] Is Null
    then @Active
    else Dual_Assignment_Table.[U/W Code]
    end AS Expr2

    case when [expr2]=Open.
    then OpenAgencies.
    else case when [expr2]=Dua.[U/W Code]
    then Dual_Assignment_Table.[U/W Code]
    else Null
    end
    end
    AS Expr3
    FROM Open_and_Closed_Agencies
    LEFT JOIN Dual_Assignment_Table ON
    Open_and_Closed_Agencies.[Agt Code] = Dual_Assignment_Table.[Agt Code]
    WHERE (((Open_and_Closed_Agencies.CloseDate)
    Is Null Or (Open_and_Closed_Agencies.CloseDate)='1/1/1900'
    Or (Open_and_Closed_Agencies.CloseDate)>GETDATE()-365))
    ORDER BY Open_and_Closed_Agencies.[U/W Code];


    the problem that i use expr2 in the same stored procedure , and this's not allowed , but how can i solve this problem ?
    thanx .
     
    memo2010, Sep 21, 2010 IP