on 2012 Aug 07 6:42 PM
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
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 )
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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.
User | Count |
---|---|
71 | |
10 | |
9 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.