SQL query statement IIF () processing condition judgment

 

Syntax

IIF (Expr,Truepart,Falsepart)

Example:

Select IIF (select =-1, "", amount) as new amount from table 1; has passed the test! In access, "-1" indicates "yes", "0" indicates "no "!

IIF
Returns two numeric values or one of the string values determined by the logical test.

Syntax
Number
IIF (logical expression, numeric expression1, numeric expression2)

If the value of logical expression is true, numeric expression1 is returned. Otherwise, numeric expression2 is returned.

String
IIF (logical expression, string expression1, string expression2)

If the value of logical expression is true, this function returns string expression1. Otherwise, string expression2 is returned.

Note
This expression is regarded as false only when the value of logical expression is zero. All other values are interpreted as true.
It is not recommended to use the IIF function to create a set of members based on search criteria. Use the filter function to evaluate each member in a specified set based on a logical expression, and then return the child set of the member.

Example
Number
If measures. currentmember is an empty unit, the following example returns 0; otherwise, 1 is returned:
IIF (isempty (Measures. currentmember), 0, 1)

String
If measures. currentmember is an empty unit, the following string returns the string "yes"; otherwise, the string "no" is returned ":
IIF (isempty (Measures. currentmember), "yes", "no ")

In access, I can use the IIF function for statistical summary. For example, to know the actual number of users that should be paid:
Select sum (IIF (amount> 0, 1, 0) as num from fee
There seems to be no corresponding function in SQL Server. I use:
Select sum (case when amount> 0 then 1 else 0 end) as num from fee
It seems not intuitive. I don't know if there are other methods.

Lee QQ; 14035344