CODEWITHSUNDEEP
mysqlsqlsql-serverif-statement
moshe
moshe

Reputation: 39

IF condition doesn't work properly

I would like to write a query using IF, but its not working , what's wrong with this query?

SELECT
IF(Emp.Name is not null)     
        ((Emp.Name) + '_' + (Emp.LastName)) as ID
else
   Emp.ID

I get this error:

Incorrect syntax near the keyword 'IF'.

Why is that?

Thank you.

Upvotes: 1

Views: 1597

Answers (5)

S.Karras
S.Karras

Reputation: 1493

You need to do this in a CASE clause:

SELECT
CASE WHEN (Emp.Name IS NOT NULL)     
    THEN ((Emp.Name) + '_' + (Emp.LastName)) 
ELSE
Emp.ID
END as ID

The IF..ELSE syntax is somewhat different:

IF(Emp.Name IS NOT NULL)    
   SELECT ((Emp.Name) + '_' + (Emp.LastName)) AS ID
ELSE
   SELECT Emp.ID AS ID

Upvotes: 2

Melchizedek
Melchizedek

Reputation: 1055

Try using Case in your query check this MySQL: CASE Function

Upvotes: 2

sagi
sagi

Reputation: 40481

You can use CASE EXPRESSION :

SELECT CASE WHEN emp.name is not null THEN emp.name + '_' + emp.lastName 
            ELSE Emp.id 
       END as [ID]
FROM ...

The IF() is performed differently in SQL-Server (I assume by the concat syntax),

IF ( Condition ) 
    SQL STATEMENT
ELSE
    SQL STATEMENT

Which means you need to perform the entire select in each block. Your kind of IF() is used in MySQL , IF(Condition , THEN , ELSE )

Upvotes: 3

Sumeet Gupta
Sumeet Gupta

Reputation: 198

Instead of usinf IF, you can use CASE in SELECT statement. Something like this

SELECT
  CASE Emp.Name
    WHEN NULL THEN Emp.ID
    ELSE CONCAT(CONCAT(Emp.Name,'_'),Emp.LastName)
    END AS "ID"
FROM Emp;

Hope it helps.

Upvotes: 0

Lulceltech
Lulceltech

Reputation: 1702

Assuming it's SQL Server based on your syntax use this:

SELECT CASE <variable> WHEN <value>      THEN <returnvalue>
                       WHEN <othervalue> THEN <returnthis>
                                         ELSE <returndefaultcase>
   END AS <newcolumnname> 
FROM <table>

Upvotes: 1

Related Questions