MSSQL select query with IF () statement

Question

I've MYSQL Query, its working fine The QUERY Is:

SELECT * , IF(totexec >= totexecrun1, totexec-totexecrun1,0)  AS rewrk, 
      SUM(tcu) tcunit , 
      IF(totexec=0, ((SUM(tcu)/totexec)*100),0) AS proflevel 
FROM mntest_schedule a 
LEFT JOIN mnrelease_details b 
   ON b.tester=a.tester 
   AND a.project=b.project 
LEFT JOIN tc_details c 
   ON b.tc_id=c.tc_name 
   AND a.project=c.project 
WHERE a.rel_name='automanual_assign' 
AND a.project='JupiterQA' 
GROUP BY a.tester;

I tried to execute the same Query in MSSQL but its throwing error. ERROR IS:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Am I did anything wrong with this query?

Look at the CASE ... WHERE... ELSE..END statement – Steve Ford Feb 03 '15 at 13:50 — Steve Ford, Feb 03 '15 at 13:50

score 1 · Answer 1 · answered Feb 03 '15 at 13:49

SELECT  * ,
        CASE WHEN totexec >= totexecrun1 THEN totexec - totexecrun1
             ELSE 0
        END AS rewrk ,
        SUM(tcu) tcunit ,
        CASE WHEN totexec = 0 THEN ( SUM(tcu) / totexec ) * 100
             ELSE 0
        END AS proflevel
FROM    mntest_schedule a
        LEFT JOIN mnrelease_details b ON b.tester = a.tester
                                         AND a.project = b.project
        LEFT JOIN tc_details c ON b.tc_id = c.tc_name
                                  AND a.project = c.project
WHERE   a.rel_name = 'automanual_assign'
        AND a.project = 'JupiterQA'
GROUP BY a.tester;

score 0 · Answer 2 · answered Feb 03 '15 at 13:51

Use CASE instead of IF. Refer this(Understanding Case Expression in SQL Server with Example) to learn CASE in SQL SERVER.

 SELECT * , 
    CASE WHEN (totexec >= totexecrun1) 
         THEN totexec-totexecrun1 
         ELSE 0 END  AS rewrk, 
    SUM(tcu) tcunit , 
    CASE WHEN (totexec=0) 
        THEN ((SUM(tcu)/totexec)*100) 
        ELSE 0 END AS proflevel 
FROM mntest_schedule a LEFT JOIN mnrelease_details b 
ON b.tester=a.tester AND a.project=b.project 
LEFT JOIN tc_details c ON b.tc_id=c.tc_name AND a.project=c.project 
WHERE a.rel_name='automanual_assign' AND a.project='JupiterQA' 
GROUP BY a.tester;

MSSQL select query with IF () statement

2 Answers2