CODEWITHSUNDEEP
mysqlsqlsql-server
festvender
festvender

Reputation: 45

Changing 'IF and NULL' from MySQL to SQL Server

I have problem in changing the query from MySQL to SQL Server. I tried to change it to ISNULL and IIF in SQL but still doesn't work. Any tips or help are welcome, thanks!

Question 1 :

MYSQL

SELECT i.item_name
         , r.item_version
         , SUM(IF(r.group_status=1,r.quantity,0)) AS `Approved`
         , SUM(IF(r.group_status=2,r.quantity,0)) AS `NotApproved`
         , SUM(r.quantity)                        AS `Total`
      FROM requesters r
      JOIN items i
        ON i.item_id = r.item_id
     WHERE r.group_status IN (1,2)
       AND r.requested_date >= '$a'
       AND r.requested_date <= '$b'
     GROUP
        BY i.item_name
         , r.item_version

SQL Server

SELECT i.item_name
     , r.item_version
     , SUM(ISNULL(r.group_status=1,r.quantity,0)) AS `Approved`
     , SUM(ISNULL(r.group_status=2,r.quantity,0)) AS `NotApproved`
     , SUM(r.quantity)                        AS `Total`
  FROM requesters r
  JOIN items i
    ON i.item_id = r.item_id
 WHERE r.group_status IN (1,2)
   AND r.requested_date >= '$a'
   AND r.requested_date <= '$b'
 GROUP
    BY i.item_name
     , r.item_version

It keep saying Incorrect syntax near ',' and still doesn't work.

Question 2 :

MYSQL

SELECT IFNULL(SUM(IF(endusers.price,endusers.price,0)),0) AS `totalprice` 
FROM endusers
WHERE requester_code = '$req_code'

SQL Server

SELECT NULLIF(SUM(COALESCE(endusers.price,endusers.price,0)),0) AS 'totalprice' 
FROM endusers 
WHERE requester_code = '$req_code'

The problem when I use COALESCE in Question 2, it won't display '0' when there's no value in the table.

Upvotes: 1

Views: 67

Answers (2)

Andrey Korneyev
Andrey Korneyev

Reputation: 26876

isnull function in SQL Server is not equivalent of if function in MySQL, use case operator instead:

SUM(case when r.group_status=1 then r.quantity else 0 end))

In the second case it looks like you're using coalesce incorrectly too since it just takes first not-null value from arguments, and it is not equivalent of MySQL if function, use case operator too.

Also in the second case you will not get 0 value when there is not result - at least because you have wrapped it with nullif and it outputs null when resulting sum is 0. So this part of your question really needs to be clarified, probably with source data sample and desired output.

Upvotes: 1

Ajay2707
Ajay2707

Reputation: 5798

In your first query, the issues are

  1. You can not give single quote() with column as given withApproved` column

  2. Your isnull condition is wrong isnull (columnname , TexttoReplace) 

      SELECT i.item_name
 , r.item_version
 , SUM(ISNULL(r.group_status, 1))  AS Approved
 , SUM(ISNULL(r.group_status,2)) AS NotApproved
 , SUM(r.quantity)  AS Total
FROM requesters r
JOIN items i
  ON i.item_id = r.item_id
WHERE r.group_status IN (1,2)
AND r.requested_date >= '$a'
AND r.requested_date <= '$b'
GROUP
BY i.item_name
 , r.item_version

Second query is not clearly understand. give the data with.

Upvotes: 1

Related Questions