开发者

SQL Server Switching Between IS NULL and = in same statement

开发者 https://www.devze.com 2023-01-06 04:46 出处:网络
Snip.. WHERE(dbo.ProductIngredients.Ing_EffectiveTo = (SELE开发者_StackOverflow中文版CTTOP (1) Ing_EffectiveTo
Snip..
    WHERE     (dbo.ProductIngredients.Ing_EffectiveTo =
                              (SELE开发者_StackOverflow中文版CT     TOP (1) Ing_EffectiveTo
                                FROM          dbo.ProductIngredients AS ProductIngredients_1
                                WHERE      (Ing_Id = dbo.ProductIngredients.Ing_Id)
                                ORDER BY (CASE WHEN Ing_EffectiveTo IS NULL THEN '01/01/2050' ELSE Ing_EffectiveTo END) DESC))

This works fine is the from the sub where clause is not null, however when null it obviously fails unless I mess with ansi_null settings (which we wont be able to do in next version of sql server so looking for better way). How can I make this query work its like I have to change from = to IS based on the result


Can't you use ISNULL(Ing_EffectiveTo,'01/01/2050') ?


Resolved it just wrapped COALESCE around both parts using 01/01/2050. This allows me to use = with NULL - incase where I dont know if value is null or not (and I care about the value if not null) - Legacy DB.

0

精彩评论

暂无评论...
验证码 换一张
取 消