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'))
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.