問題描述

我正在嘗試創建一個 WHERE 子句，如果變量滿足特定要求，它將觸發特定條件

I'm trying to create a WHERE clause that would trigger specific conditions if a variable meet specific requirements

 WHERE 
     CASE 
      WHEN @Restriction = '1' THEN CFE_EDI.ETAT = 'ENV' OR CFE_EDI.ETAT = 'OUV'
      WHEN @Restriction = '0' THEN CFE_EDI.ETAT <> 'ENV' AND CFE_EDI.ETAT <> 'OUV'
      ELSE CFE_EDI.ETAT != '' AND CFE_EDI.CODE_INSEE IS NOT NULL 
     END

如您所見，它不起作用，因為 CASE 在 THEN

As you can see, it won't work as CASE does not work well with having conditions after the THEN

我的一個朋友想出的一個解決方案是

One solution , a friend of mine came up with , would be that

WHERE (@restriction = 1 AND CFE_EDI.ETAT IN ('ENV', 'OUV'))
OR (@restriction = 0 AND CFE_EDI.ETAT.ETAT NOT IN ('ENV', 'OUV'))
OR (CFE.EDI.ETAT <> '' AND CFE_EDI.CODE_INSEE IS NOT NULL)       

然而，想想看，邏輯是如果@Restriction 等于1，那么把條件CFE_EDI.ETAT = 'ENV' OR CFE_EDI.ETAT = 'OUV' 和提出的解決方案由我的朋友不這樣做.

Yet, thinking about it, the logic would be if @Restriction is equal to one then put the conditions CFE_EDI.ETAT = 'ENV' OR CFE_EDI.ETAT = 'OUV' and the solution proposed by my friend does not do that.

我知道可以在 Crystal Reports 中完成，但我不確定是否可以在 SQLServer 中完成.

I know it can be done in Crystal Reports but I'm unsure there is way of doing it in SQLServer.

謝謝

推薦答案

您的朋友解決方案已經適用于您的方案，但我不明白為什么您說它不起作用.你可以試試這個，但它類似于你朋友的解決方案.

Already your friend solution would work for your scenario but I don't why are you telling that it doesn't work. You can try this but it is similar to your friend solution.

WHERE ((@restriction = 1    AND (CFE_EDI.ETAT='ENV' OR CFE_EDI.ETAT='OUV'))
         OR (@restriction = 0   AND (CFE_EDI.ETAT<>'ENV' AND CFE_EDI.ETAT<>'OUV'))
         OR (CFE.EDI.ETAT <> '' AND CFE_EDI.CODE_INSEE IS NOT NULL))

這篇關于sqlserver - 如果變量滿足要求，它將觸發特定條件的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！