Sql server where with if and else

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I want to perform like this but getting error

MY QuERY IS like

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

GOT ERROR LIKE

Incorrect syntax near '='.

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

CalStatu1='Done' and CalDis='Pending'

otherwise if will check by

CalFinal='True'

Please reply

What I have tried:

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

Posted 24-Apr-19 19:09pm

hareshdgr8

Updated 24-Apr-19 22:03pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2019-04-24T22:04:00

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.

SQL

select * from TableNm where

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

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.

SQL

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

Kevheathfield · Accepted Answer · 2019-04-24T19:36:00

Solution 1

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.

Posted 24-Apr-19 19:36pm

Kevheathfield

Comments

hareshdgr8 25-Apr-19 1:49am

no sir same getting error over CalStatu1='Done' and CalDis='Pending'

Hide Copy Code

Incorrect syntax near '='.

Kevheathfield 25-Apr-19 2:23am

My apologies, I realized there is syntax I missed. (A bit hard without an IDE)

Please try;

select * from TableNm
where CASE WHEN cal1 = 'true' THEN 
CalStatu1 = 'Done' and cal1 = 'true' else CalFinal = 'True' END

hareshdgr8 25-Apr-19 2:58am

no sir same error

Incorrect syntax near '='.

Kevheathfield 25-Apr-19 4:12am

Hmm, I can run this on a MySql instance..
Here is my last ditch attempt, please keep in mind that this is not optimal, but based on the column names in the question, I am under the assumption that this is for a home project.

select * from tablenm
WHERE(cal1 = 'true' AND CalStatu1 = 'Done' and cal1 = 'true')
OR  (cal1 != 'true' AND CalFinal = 'True'));

CHill60 25-Apr-19 4:57am

WHERE(cal1 = 'true' AND CalStatu1 = 'Done' and cal1 = 'true')

should be

WHERE (CAl1='True' AND CalStatu1='Done' AND CalDis='Pending')

You're testing CAl1 twice

hareshdgr8 25-Apr-19 4:40am

THis is work for me but not sure how much right but thank you for your help ...