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
– Steve Ford

2015-02-03 13:50:41 +00:00
Commented Feb 3, 2015 at 13:50 — Steve Ford
– Steve Ford, Commented Feb 3, 2015 at 13:50

Dmitrij Kultasev · Accepted Answer · 2015-02-03 13:49:36Z

1

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;

answered Feb 3, 2015 at 13:49

Dmitrij Kultasev

5,8658 gold badges53 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Saravana Kumar · Accepted Answer · 2015-02-03 13:51:01Z

0

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;

answered Feb 3, 2015 at 13:51

Saravana Kumar

3,7295 gold badges19 silver badges39 bronze badges

Collectives™ on Stack Overflow

MSSQL select query with IF () statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related