Post reply

Case statement and if not exists

  • March 27, 2011 at 2:59 pm

    #389149

    update STGtable

    Set processKey = Case

    When table1.DataValue is NULL or table1.DataValue = ' ' THEN 0

    When (not exists Dimprocess.processKey) Then -1

    ELSE (select max(ProcessKey) from DimProcess m where m.processCode = table1.DataValue)

    In this Query i am trying to do, if the processkey value in the Dimprocess table does not exist then the ProcessKey in the STGtable should be -1.

    Please help me out with this Query.

    thanks.

  • March 27, 2011 at 3:08 pm

    #1304128

    update STGtable

    Set processKey = CASE

    WHEN table1.DataValue is NULL or table1.DataValue = ' ' THEN 0

    WHEN NOT EXISTS (SELECT 1 FROM DimProcess m where m.processCode = table1.DataValue) THEN -1

    ELSE (select max(ProcessKey) from DimProcess m where m.processCode = table1.DataValue)

    END

    FROM STGtable INNER JOIN table1 ON ....

    Not tested obviously, but should give you an idea. Be aware that if the subquery returns more than one row, the query will fail.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 28, 2011 at 8:37 am

    #1304400

    Thanks

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

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