Post reply

IF statement / Variables.. problems!

  • September 18, 2012 at 4:15 am

    #267872

    Hello

    I am trying to write a view and have a column that looks for a value in a column, if it finds it then, move onto the next etc...

    When it finds the value it is looking for I want it to return the value contained in another column.

    EG:

    Table Headings:

    Type 1, Type 2, Type 3, Type 4, Detail 1, Detail 2, Detail 3, Detail 4

    So it looks for a value in the Type column, when it finds it, it moves on to the next Type. When it doesn't find the value, it returns the Detail column. If it finds the value in all columns, then it returns "All" or some such.

    I can work it out in Excel, but I'm new to SQL Server 2005 so it is a bit of a battle.

    Thanks

    Peter

  • September 18, 2012 at 4:39 am

    #1538995

    can you past in some DDL and representative data so that we can see what your trying to explain?

    ***The first step is always the hardest *******

  • September 18, 2012 at 4:47 am

    #1538997

    The "Type" fields contain various categories in a 3 number/figure format.

    I'm looking for the first Type field where "RFS" does NOT appear.

    When it finds this first occurance, I want to create a column that returns the corresponding detail.

    So I want it to look at existing columns and return the result in a new column...

    I don't know if that explains it better. Let me know what else you would want to see.

    Regards

    Peter

  • September 18, 2012 at 4:52 am

    #1539000

    So the equivalent in Excel is:

    =IF(Type1="RFS",

    IF(Type2="RFS",

    IF(Type3="RFS",

    IF(Type4="RFS","ALL",Detail4),

    Detail3),

    Detail2),

    Detail1)

    Sorry for format, I'm a little unfamiliar with the forum layout

  • September 18, 2012 at 9:42 am

    #1539161

    I guess you can do this or several nested CASEs as you had on Excel.

    CASE WHEN Type1 <> 'RFS' THEN Detail1

    WHEN Type2 <> 'RFS' THEN Detail2

    WHEN Type3 <> 'RFS' THEN Detail3

    WHEN Type4 <> 'RFS' THEN Detail4

    ELSE 'ALL' END

    When a condition evaluates TRUE, the following conditions will be ignored.

    What would happen when you have 'RFS' in Type2 and Type4? What would be your precedence?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • September 18, 2012 at 9:53 am

    #1539167

    perhaps like this?

    select case when @type1 = 'RFS' THEN

    case when @type2 = 'RFS' THEN

    case when @type3 = 'RFS' THEN

    case when @type4 = 'RFS' THEN 'ALL' ELSE @type4 END ELSE @type3 END ELSE @type2 END ELSE @type1 END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply