SQL Case Statement Question

Posted on 2007-10-17
Last Modified: 2008-01-09
I have a query that pulls back a recordset based on a user provided organization code.  If the code is 4 characters long it needs to match one record, if 5 then another, and if 'All' then it just needs to bring back everything.  I'm using the following:

SELECT ROA.Resource_Organization_Assignment_ID, ROA.Five_Dot, RT.Resource_Type_Name, R.Name_Last,
ISNULL(Name_Clarity, R.Name_First) AS First_Name, ROA.Rate_Hourly
FROM t_Resource R, t_Resource_Organization_Assignment ROA, t_Resource_Type RT
WHERE ROA.Resource_ID = R.Resource_ID
AND ROA.Resource_Type_Code = RT.Resource_Type_Code
AND ROA.Resource_Type_Code = 'T'
AND ISNULL(ROA.Date_Start,'01/01/2000') <=  CAST('12/31/' + @Year AS DATETIME)
AND ISNULL(ROA.Date_End, '01/01/2999') >= CAST('01/01/' + @Year AS DATETIME)

CASE WHEN Org_Length = 5 THEN
      AND ROA.Five_Dot = @Org_Code
WHEN Org_Length = 4 THEN
      AND ROA.Four_Dot = @Org_Code

I'm not totally sure this can be done like this but it would help spare me from having the entire query in the SP twice and then mixing in an IF statement.

Thanks in advance,

Question by:stretch73
    LVL 21

    Accepted Solution

    AND ( Org_Length = 5 And ROA.Five_Dot = @Org_Code
      Or Org_Length = 4 AND ROA.Four_Dot = @Org_Code
      Or Org_Length = not sure how you indicate all here )
    LVL 21

    Expert Comment

    If you're keeping a string in Org_Length then

    AND ( Org_Length = '5' And ROA.Five_Dot = @Org_Code
      Or Org_Length = '4' AND ROA.Four_Dot = @Org_Code
      Or Org_Length = 'ALL' )

    Author Comment


    That's a good start.  I forgot to include this:

    DECLARE @Org_Length INT

    SET @Org_Length = LEN(@Org_Code)

    So it's an integer.  

    Author Comment

    It's bringing back a huge recordset, trying to pin it down.
    LVL 19

    Assisted Solution

    As a case clause,
    where @Org_Code = case len(@Org_Code) when 5 then ROA.Five_Dot when 4 then ROA.Four_Dot else case when @Org_Code = 'All' then @Org_Code end end

    however, basing it on mastoo's post is better,

    AND(LEN(@Org_Code) = 5 And ROA.Five_Dot = @Org_Code
      Or LEN(@Org_Code) = 4 AND ROA.Four_Dot = @Org_Code
      Or @Org_Code = 'ALL' )

    Author Comment

    Thanks guys, that ended up doing it.  Much simpler than I was making it.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now