CODEWITHSUNDEEP
sql-servermssql-jdbc
user3114967
user3114967

Reputation: 629

MSSQL select query with IF () statement

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?

Upvotes: 0

Views: 3226

Answers (2)

Saravana Kumar
Saravana Kumar

Reputation: 3729

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;

Upvotes: 0

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

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;

Upvotes: 1

Related Questions