Post reply

converting Iif from access 2007 to SQL Server 2005

  • May 18, 2010 at 2:03 pm

    #221770

    I'm in the process of converting an access Iif to SQL Server 2005. Is it possible to do an update with multiple case whens in the query?

    This is the code from access that i'm trying to convert to SQL

    UPDATE shiptype

    SET shiptype.[Order Type] = Iif([EDI_T] LIKE "web*", "WEB", Iif([FrctStnd] = 1, "EDI", Iif([EDI_T] IS NULL, "MAN", "EDI")))

    This is what I was trying to do. Am I on the right track?

    UPDATE [shiptype]

    SET [shiptype].[Order Type] = [EDI_T]

    CASE WHEN ([shiptype].[EDI_T]) LIKE 'web%'

    THEN 'WEB'

    ELSE NULL

    END,

    CASE WHEN ([shiptype].[FrctStnd]) = '1'

    THEN 'EDI'

    ELSE NULL

    END,

    CASE WHEN ([shiptype].[EDI_T]) IS NULL

    THEN 'MAN'

    ELSE 'EDI'

    FROM [shiptype]

  • May 18, 2010 at 2:42 pm

    #1169113

    Untested, so you definitely need to verify in detail:

    UPDATE [shiptype]

    SET [shiptype].[Order Type] = [EDI_T]

    CASE

    WHEN ([shiptype].[EDI_T]) LIKE 'web%' THEN 'WEB'

    WHEN ([shiptype].[FrctStnd]) = '1' THEN 'EDI'

    WHEN ([shiptype].[EDI_T]) IS NULL THEN 'MAN'

    ELSE 'EDI'

    END

    FROM [shiptype]


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

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