CODEWITHSUNDEEP
mysql
Al Kasih
Al Kasih

Reputation: 886

Change Null with 0

I have tried to run a query I phpmyAdmin as follows

SELECT
  orders_history.id,
  orders_history.`items`,
  orders_history.`transaction_id`,
  orders_history.`quantity`,
  estockClaims.`esquantity`,
  IFNULL( esquantity, 0 ),
  orders_history.`quantity` - estockClaims.`esquantity` AS myquantity
FROM orders_history
LEFT JOIN estockClaims
  ON orders_history.transaction_id = estockClaims.transaction_id
 AND orders_history.items = estockClaims.items
LIMIT 0 , 100

And it gives me this result: 

       ----------------------------------------------------------------------
       id     items      transaction_id    quantity   esquantity   IFNULL(esquantity , 0 )   myquantity
       1      FR               001           100        NULL              0                        NULL
       2      BR               002            10        NULL              0                        NULL
       3      WR               003            25        25               25                        0
       4      CR               004            50        3                 3                        47

How to solve this so that NULL is not NULL anyomre but change to 0. Thanks in advance.

Thanks

Upvotes: 1

Views: 147

Answers (5)

Shomz
Shomz

Reputation: 37701

You already have it in the next column. What you need to do is to drop the original esquantity column and make an alias for the IFNULL... column, like this: 

SELECT orders_history.id, orders_history.`items` , orders_history.`transaction_id` , 
       orders_history.`quantity` , IFNULL( esquantity, 0 ) AS esquantity, 
       orders_history.`quantity` - estockClaims.`esquantity` AS myquantity
FROM orders_history
LEFT JOIN estockClaims ON orders_history.transaction_id = estockClaims.transaction_id
AND orders_history.items = estockClaims.items
LIMIT 0 , 100

The change I mentioned is in the line 2 above.

UPDATE

To get 

orders_history.`quantity` - estockClaims.`esquantity` AS myquantity

to show expected results, you need to "unnullify" the esquantity field again so that the subtraction would work: 

orders_history.`quantity` - IFNULL( estockClaims.`esquantity`, 0 ) AS myquantity

That would ensure you no longer get, for example: 

100 - NULL

but this instead: 

100 - 0

which will return a proper value.

You can also skip the whole subtraction thing if esquantity is NULL and simply use the value of quantity.

Upvotes: 4

Saravana Kumar
Saravana Kumar

Reputation: 3729

You can also use COALESCE to replace the NULL value to 0

check this query.

SELECT orders_history.id, orders_history.`items` , orders_history.`transaction_id` , 
       orders_history.`quantity` , COALESCE( esquantity, 0 ) AS esquantity, 
       orders_history.`quantity` - COALESCE(estockClaims.`esquantity`, 0) AS myquantity
FROM orders_history
LEFT JOIN estockClaims ON orders_history.transaction_id = estockClaims.transaction_id
AND orders_history.items = estockClaims.items
LIMIT 0 , 100

Upvotes: 0

SonalPM
SonalPM

Reputation: 1337

select temp.id, items, transaction_id, quantity, ifNULL(esquantity, 0), ifNULL(myquantity, 0)
from (SELECT
  orders_history.id,
  orders_history.`items`,
  orders_history.`transaction_id`,
  orders_history.`quantity`,
  estockClaims.`esquantity`
  orders_history.`quantity` - estockClaims.`esquantity` AS myquantity
FROM orders_history
LEFT JOIN estockClaims
  ON orders_history.transaction_id = estockClaims.transaction_id
 AND orders_history.items = estockClaims.items
LIMIT 0 , 100) temp

Upvotes: 0

SMA
SMA

Reputation: 37023

Reason is you are using it as select instead of when doing subtraction. Use it as below:

SELECT orders_history.id, orders_history.`items` , orders_history.`transaction_id` ,          orders_history.`quantity` , orders_history.`quantity` - IFNULL( esquantity, 0 ) AS myquantity
FROM orders_history
LEFT JOIN estockClaims ON orders_history.transaction_id = estockClaims.transaction_id
AND orders_history.items = estockClaims.items
LIMIT 0 , 100

Upvotes: 0

No Idea For Name
No Idea For Name

Reputation: 11577

you can use IF to check the esquantity and myquantity columns:

IF(esquantity IS NULL, 0, esquantity)

and

IF(myquantityIS NULL, 0, myquantity)

or use IFNULL as DanFromGermany said

Upvotes: 0

Related Questions