Post reply

Case statement and unique index

  • May 11, 2005 at 2:49 am

    #109586

    For the proper use of a application I developed, a unique index has to be created on 2 colums(wwoverzcode_2 & wwoactsubcode). However, because this table has several levels(top -> parent -> child -> ....etc -> node) this index has to be manipulated for some records.

    Because my application has to support both Oracle as SQL Sever, I've already thought out the following for my oracle databases:

    create unique index twwo_code on twwo (

       case when (wwoverzcode_2<>'') THEN wwoverzcode_2 ELSE wwocode END asc,

       wwoactsubcode asc

    )

    / 

    This works fine!

    Now I want this also to work i.c.w. SQL Server. However when using the code below I get a syntax error on the case statement. What's wrong? Isn't this allowed in SQL Server?

    create unique  index twwo_code on twwo (

       case when (wwoverzcode_2<>'') THEN wwoverzcode_2 ELSE wwocode END,

       wwoactsubcode

    )

    go 

     

  • May 11, 2005 at 4:11 am

    #557998

    No it is not valid syntax.

    You could try creating a view and then indexing that.

    LinkedIn Profile

  • May 11, 2005 at 4:43 am

    #558007

    Or you could add a computed column and use that in the index.

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • May 11, 2005 at 7:04 am

    #558052

    Is it possible to replace the case statement with anyother syntax that can do the same thing?

Viewing 4 posts - 1 through 3 (of 3 total)

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