1

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.

Improve this question
2
  • Don't use backquotes in SQLServer Commented May 19, 2016 at 9:50
  • @festvender In first query I think you need to sum with 2different set of data and in second query you sum with check that if does not have value return 0. Right? if yes, why you use coalesce? Commented May 19, 2016 at 9:58

2 Answers 2

Reset to default
1

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.

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

6 Comments

Ive change it and how to solve the 'Operand data type varchar is invalid for sum operator.' came out?
@festvender change the data type from text to numerical : sum(cast(varchar column as int)) - Or as whatever numerical datatype you want
Well, if you're getting such an errors - it means your r.quantity value has been stored as varchar data type in DB. If it is intended - then just use cast(r.quantity as int) instead of r.quantity. But generally storing numerics as varchar should be avoided.
@AndyKorneyev Its work but still not stable I think, Do you mind to take a look at it ? pastebin.com/g636EMJ2 , If there any wrong regarding that query please let me know. Thanks!
As far as I can see, you're still using r.quantity in conditional sums with case operator and casting it to int only in Total column. You have to cast it to int in every place you're using sum. Or, as I've already said - just change DB column type from varchar to int.
|
1

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.

Improve this answer

9 Comments

About the second, please look here sqlfiddle.com/#!3/479fb/1, Ive have problem when there are decimal number.
@festvender in your sample you're having your price column type as varchar, but actually storing decimal values in it. Is there any reason for it? Anyway, if you cant' change your DB structure and it forces you to use varchar for the price - then change COALESCE(endusers.price,0) to COALESCE(cast(endusers.price as decimal(10, 2)),0).
@AndyKorneyev what other data type that suitable in my case if I want to change the DB structure?. (Ex: 200.97)
float or numeric(18,2)
@festvender i wouldn't recommend to use float data type for financial calculations since it is "approximate" type. Use decimal(x,y) where x is maximum size of your numbers and y is precision. In your particular case it is somthing like decimal(10, 2) or similar.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.