Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    3

    Unanswered: How to recall column name defined in CASE function

    Hello all,

    I need to refine a query in which one of the search conditions would depend on the value evaluated from the CASE function in SELECT statement. This returned column is named as "SLA". Now, the problem is I don't know how to recall this column in the WHERE clause as to do refinement. When I code it like SLA = @Term, SQL Server returned an error: Invalid column name 'SLA'

    If anyone knows a solution, please kindly let me know.

    Thank you!

    Here is the sample code:

    ALTER PROCEDURE [dbo].[sp_premium_register]
    @PolicyType AS VARCHAR(10),
    @ReportFrom AS DATETIME,
    @ReportTo AS DATETIME,
    @Business AS VARCHAR(1),
    @Term AS VARCHAR(1)

    SELECT
    ColumnA,
    ColumnB,

    SLA =
    CASE
    WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) > 1 THEN 'L'
    WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) <= 1 THEN 'S'
    END

    FROM DNIssue D
    LEFT OUTER JOIN KILRIShare S
    ON (D.PolicyNo = S.PolicyNo AND D.PolicyType = S.PolicyType AND D.Origin = S.Origin AND D.EndorsementNo = S.EndrNo AND D.PartyNo = S.RINo)
    LEFT OUTER JOIN KILPolicy P
    ON (D.PolicyNo = P.PolicyNo AND D.PolicyType = P.PolicyType AND D.Origin = P.Origin AND D.EndorsementNo = P.EndrNo)
    LEFT OUTER JOIN v_report_KILDNFund F
    ON (D.DebitNote = F.DebitNote)
    LEFT OUTER JOIN PolicyProfile R
    ON R.Origin = D.Origin AND R.PolicyType = D.PolicyType


    WHERE
    SLA = @Term

    Order by D.PolicyType, D.DebitNote, D.Origin, D.PolicyNo, D.EndorsementNo, D.EntryDate

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use an outer query
    Code:
    SELECT ColumnA
         , ColumnB
         , SLA 
      FROM (
           SELECT ColumnA
                , ColumnB
                , SLA = 
                  CASE 
                  WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) > 1 
                  THEN 'L'
                  else 'S'
                  END
             FROM DNIssue D
           LEFT OUTER 
             JOIN KILRIShare S
               ON S.PolicyNo   = D.PolicyNo      
              AND S.PolicyType = D.PolicyType     
              AND S.Origin     = D.Origin        
              AND S.EndrNo     = D.EndorsementNo 
              AND S.RINo       = D.PartyNo       
           LEFT OUTER 
             JOIN KILPolicy P
               ON P.PolicyNo   = D.PolicyNo 
              AND P.PolicyType = D.PolicyType 
              AND P.Origin     = D.Origin 
              AND P.EndrNo     = D.EndorsementNo
           LEFT OUTER 
             JOIN v_report_KILDNFund F
               ON F.DebitNote = D.DebitNote
           LEFT OUTER 
             JOIN PolicyProfile R
               ON R.Origin = D.Origin 
              AND R.PolicyType = D.PolicyType
           ORDER 
               BY D.PolicyType
                , D.DebitNote
                , D.Origin
                , D.PolicyNo
                , D.EndorsementNo
                , D.EntryDate
           ) as t
     WHERE SLA = @Term
    or bite the bullet and code the datediff into the WHERE condition
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    you'll have to repeat the case statement in your where clause

  4. #4
    Join Date
    Aug 2007
    Posts
    3
    It works! Thank you so much!!!

Posting Permissions

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