sql server在哪里用if和else [英] Sql server where with if and else

问题描述

我想这样表演却收到错误



我很奇怪

I want to perform like this but getting error

MY QuERY IS like

从TableNm中选择*，其中iif（CAl1 ='True'，（CalStatu1 ='Done'和CalDis ='待定'），（CalFinal ='True'））





GOT ERROR LIKE

select * from TableNm where iif(CAl1='True',(CalStatu1='Done' and CalDis='Pending'),(CalFinal='True'))


GOT ERROR LIKE

Incorrect syntax near '='.

i想如果CAL1为真，那么Condition将通过

i want like if CAL1 is true then where Condition will check by

CalStatu1='Done' and CalDis='Pending'

否则如果将通过

CalFinal='True'

请回复



我尝试过：

Please reply

What I have tried:

select * from TableNm where   iif(CAl1='True',(CalStatu1='Done' and CalDis='Pending'),(CalFinal='True')) 

推荐答案

你不需要IIF并且你不需要CASE，你只需要正确地对你的AND和OR进行排序，例如

You don't need an IIF and you don't need CASE, you just need to get your AND and OR sorted properly e.g.

select * from TableNm where

(CAl1='True' AND CalStatu1='Done' AND CalDis='Pending')
OR
(Cal1 <> 'True' AND CalFinal='True')

请注意使用括号将AND条件组合在一起。将AND和OR混合在一起没有括号是一个非常常见的错误。总是使用括号来阐明你的意图。



另一方面，如果你想更新列，那么你必须一次一个地进行更新声明，例如

Note the use of brackets to group together the AND criteria. Mixing AND and OR together without brackets is a very common mistake. Always use brackets to clarify your intention.

On the other hand, if you are trying to update columns then you have to do them one at a time and in an Update statement e.g.

UPDATE TableNm
SET CalStatu1 = CASE WHEN Cal1 = 'True' THEN 'Done' ELSE CalStatu1 END,
	CalFinal = CASE WHEN CAl1 = 'True' THEN CalFinal ELSE 'True' END

我想你可能会误解Iif命令。

文档状态s：

Hi,

I think you may be misinterpreting the Iif command.
The documentation states:

boolean_expression
A valid Boolean expression.

If this argument is not a Boolean expression, then a syntax error is raised.

true_value
Value to return if boolean_expression evaluates to true.

false_value
Value to return if boolean_expression evaluates to false.

因此，你的语法

Therefore, your syntax

select * from TableNm where   iif(CAl1='True',(CalStatu1='Done' and CalDis='Pending'),(CalFinal='True'))

不正确true_value实际上是一个返回值。



将此更改为case语句是非常明显的，以便使您的项目/查询可以与所有SQL服务器一起移植实例。



我很遗憾当前没有安装MSSQL，但也许下面会有所帮助;

is incorrect in that the true_value is actually a return value.

It is highly suggestible to change this to a case statement, in order to make your project/query portable with all SQL server instances.

I unfortunately do not have MSSQL installed at current, but perhaps the below will assist;

select * from TableNm where
case cal1 = 'true' 
	then CalStatu1='Done' and CalDis='Pending'
else
	CalFinal='True'
end 

希望这会有所帮助。

Hope this helps.

这篇关于sql server在哪里用if和else的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！