Query returning NULL - Please help with this

[Former Member]

DELIMITER $$

DROP PROCEDURE IF EXISTS lunasoft.kalkulacija$$

CREATE DEFINER=root@localhost PROCEDURE kalkulacija()

BEGIN

update aos_products_cstm as t1

set t1.kolicina_c=(select sum(t2.product_pri) from aos_products_quotes as t2 where t2.product_id=t1.id_c and t2.deleted=0 and pro_usluga_c='proizvod' group by t2.product_id) - (select sum(t2.product_rac) from aos_products_quotes as t2 where t2.product_id=t1.id_c and t2.deleted=0 and pro_usluga_c='proizvod' group by t2.product_id);

END$$

DELIMITER ;

DELIMITER $$

DROP TRIGGER /!50032 IF EXISTS / lunasoft.racun_unos$$

CREATE /!50017 DEFINER = 'root'@'localhost' / TRIGGER racun_unos AFTER INSERT ON aos_products_quotes FOR EACH ROW BEGIN call kalkulacija; END; $$

DELIMITER ;

DELIMITER $$

DROP TRIGGER /!50032 IF EXISTS / lunasoft.racun_izmjena$$

CREATE /!50017 DEFINER = 'root'@'localhost' / TRIGGER racun_izmjena AFTER UPDATE ON aos_products_quotes FOR EACH ROW BEGIN call kalkulacija; END; $$

DELIMITER ;

DELIMITER $$

DROP TRIGGER /!50032 IF EXISTS / lunasoft.racun_brisanje$$

CREATE /!50017 DEFINER = 'root'@'localhost' / TRIGGER racun_brisanje AFTER DELETE ON aos_products_quotes FOR EACH ROW BEGIN call kalkulacija; END; $$

DELIMITER ;

Please help with this kolicina_c only be shown if both fields are filled and product_pri product_rac. I would like to make kolicina_c displayed if only one field filled. Thanks

[Former Member]

You need to modify where the Coalesce is used. It looks like you've combined the embedded select in your set clause from the original; I will assume that's valid. It is certainly simpler to look at. Modifying the latest version I see:

SET t1.kolicina_c=(SELECT COALESCE(SUM(t2.product_pri),0) -COALESCE(SUM(t2.product_rac),0) FROM aos_products_quotes AS t2 WHERE t2.product_id=t1.id_c AND t2.deleted=0 AND pro_usluga_c='proizvod' GROUP BY t2.product_id);

[Breck_Carter]

Is this a question about MySQL?

[Former Member]

If I understand correctly, if either expression in your update statement above are null you get a null result.

Depending on the semantics of a null in your system, you may want the Coalesce function. It looks like you are summing some values, so it may be reasonable to assume a null value is equivalent to zero. On the other hand, if there are no values in any of the rows that create the sum (or associated rows from the table you are summing) you have missing data, and using 0 may be misleading. You will have to decide.

So your first expression in the update may be:

COALESCE( (select sum(t2.product_pri) from aos_products_quotes as t2 where t2.product_id=t1.id_c and t2.deleted=0 and pro_usluga_c='proizvod' group by t2.product_id), 0 )

[Former Member]

Ron thanks a lot for your response. This is my formula for a warehouse, where the field kolicina_c stock of a product. Product_pri field is related to the receipt, and field product_rac is related to the invoice. An example of a product beer does not have stocks, until you make invoice and receipt. Condition for the product to be displayed when only one document is created.

[justin_willey]

It work just making the point that SUM() ignores individual rows that have null values, but if all the rows are null or if there are no rows then SUM() will return a null. If, as in your example, you then try to use that value in an operation with other non-null values, you will always get an overall null - ie NULL + x = NULL

see http://dcx.sybase.com/index.html#1001/en/dbrfen10/rf-sum.html

and http://dcx.sybase.com/index.html#1001/en/dbrfen10/rf-nulls.html

[Former Member]

Sounds like 0 is the answer you want in case of nulls then. Justin did a better job of explaining why you are getting a null, but the Coalesce function should take care of the symptoms.

For docs see http://dcx.sybase.com/index.html#1201/en/dbreference/coalesce-function.html That's for SA 12.0.1 but it's been basically the same for as many major versions as I can recall.