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.
精彩评论