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

asked 07 Aug '12, 17:42

Joseph's gravatar image

Joseph
45113
accept rate: 0%

edited 08 Aug '12, 12:11

Justin%20Willey's gravatar image

Justin Willey
7.0k116149220

Is this a question about MySQL?

(12 Aug '12, 08:38) Breck Carter

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 )
permanent link

answered 07 Aug '12, 20:20

Ron%20Emmert's gravatar image

Ron Emmert
33651118
accept rate: 12%

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.

(08 Aug '12, 03:49) Joseph
Replies hidden

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

(08 Aug '12, 07:02) Justin Willey
1

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.

(08 Aug '12, 11:29) Ron Emmert
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×61
×19
×5

question asked: 07 Aug '12, 17:42

question was seen: 1,345 times

last updated: 12 Aug '12, 08:38