Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2010
    Posts
    19

    Unanswered: sql storedprocedure problem

    in my stored procedure , i have many values output , but if i need to use one of them in the same stored procedure , he tell me that he cann't see the variable !

    suppose i make this ,
    case full= null
    then 5
    else 6
    end As testfull

    case testfull = ...........
    here , he tell me he cann't see testfull , so what is the solution ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your syntax is all messed up, it should be like this --
    Code:
    CASE WHEN full IS NULL
         THEN 5 
         ELSE 6
     END AS testfull
    if you then wish to evaluate testfull, you can use this --
    Code:
    CASE WHEN full IS NULL /* testfull = 5 */
         THEN ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2010
    Posts
    19

    sql

    noooo , not that what i mean ,
    i just write it as simple , look at my problem ,
    here is the code

    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 @ActiveTerritory
    else Dual_Assignment_Table.[U/W Code]
    end AS Expr2

    case when [Expr2]=Open_and_Closed_Agencies.[U/W Code]
    then Open_and_Closed_Agencies.[U/W Code]
    else case when [Expr2]=Dual_Assignment_Table.[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];


    he tell me he can't see expr2 , this's my prblem
    thnx

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Looks to me like you copied this query from MS Access and you are expecting SQL to behave using Access syntax--it will not.

    In Access, later in a query, you can refer to a previously calculated value in that same query. In SQL you cannot.

    In other words, you are assuming:

    Code:
    SELECT   CaluclateExpr2 AS Expr2
            ,Case
                When Expr2 Is This
                Then DoThis
                Else DoThis
            End
    You cannot do that is SQL. You have to do this:

    Code:
    SELECT   CaluclateExpr2 AS Expr2
            ,Case
                When CaluclateExpr2 Is This
                Then DoThis
                Else DoThis
            End
    Last edited by PracticalProgram; 09-21-10 at 11:51.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Sep 2010
    Posts
    19

    sql

    yes , i agree with you ,
    but as you see from my code , it's complex and i can't just rewrite it in the case statement ,
    don't you have another solution ?

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Believe me, this is not complex.

    If I can do it, you can do it.

    Here it is (cleaned-up a bit):

    Code:
    SELECT   tOCA.[U/W Code] agt_uw_code
             ,tOCA.[Agt Code]
             ,tDA.[U/W Code] dual_uw_code
             ,case
                 when tDA.[U/W Code] Is Null then @ActiveTerritory
                 else tDA.[U/W Code]
             end Expr2
             ,case
                 when
                     case
                         when tDA.[U/W Code] Is Null then @ActiveTerritory
                         else tDA.[U/W Code]
                     end=tOCA.[U/W Code] then tOCA.[U/W Code]
                 else
                     case
                         when [Expr2]=tDA.[U/W Code] then tDA.[U/W Code] 
                         else Null
                     end
             end Expr3
    FROM     Open_and_Closed_Agencies tOCA
    LEFT
    JOIN     Dual_Assignment_Table tDA ON 
                 tOCA.[Agt Code]=tDA.[Agt Code]
    WHERE    tOCA.CloseDate) Is Null
             Or tOCA.CloseDate='1/1/1900'
             Or tOCA.CloseDate>GETDATE()-365
    ORDER
    BY       tOCA.[U/W Code]
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Sep 2010
    Posts
    19
    ya , ur right , it's not complex , it's work now as you suggest
    thnx

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •