[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2075
  • Last Modified:

subtraction inside a stored procedure in mysql

I am trying to create two stored procedures that will go into my database and get the qty used and the qty received for my inventory.  

this is the procedure i created to get the total received:

DELIMITER $$

DROP PROCEDURE IF EXISTS `inventory`.`spTotalReceived` $$
CREATE PROCEDURE `spTotalReceived`(upc INT)
BEGIN
SELECT sum(p.qty) as 'total received' FROM parts_used p WHERE status = 0 and upc = upc;
END $$

DELIMITER ;

it should return a value of 4, but instead returns 718

this is the procedure to give me what has been used

DELIMITER $$

DROP PROCEDURE IF EXISTS `inventory`.`spTotalReceived` $$
CREATE PROCEDURE `spTotalReceived`(upc INT)
BEGIN
SELECT sum(p.qty) as 'total received' FROM parts_used p WHERE status = 0 and upc = upc;
END $$

DELIMITER ;

the only difference is that the status is 0 and not 1

this also gives me a value of 718 when i do this

call spTotalUsed(780672318921)

and it should be 3.

My end goal is to subtract those two values and return a on hand quantity

How do I do that?
0
revo1059
Asked:
revo1059
  • 10
  • 9
1 Solution
 
Richard DavisSenior Web DeveloperCommented:
First of all, both of your procedures are the exact same procedure. Secondly, what is the data type of the upc variable, because if I'm not mistaken, an INT in MySQL can only represent upto a value of 2147483647 and your number is
780672318921 which is far bigger.

I would try changing your line that reads;
CREATE PROCEDURE `spTotalReceived`(upc INT)

to this

CREATE PROCEDURE `spTotalReceived`(upc BIGINT)

because that data type can handle a value up to 9223372036854775807
0
 
Richard DavisSenior Web DeveloperCommented:
Also, I failed to mention, that if you're going to use an alias for you table in your FROM clause, you should use that alias in every field reference in your sql statement also.

So, as an example, you should change this;
SELECT sum(p.qty) as 'total received' FROM parts_used p WHERE status = 0 and upc = upc;
to this
SELECT sum(p.qty) as 'total received' FROM parts_used p WHERE p.status = 0 and p.upc = upc;
or else just do this
SELECT sum(qty) as 'total received' FROM parts_used WHERE status = 0 and upc = upc;

But never mix them up because the DB engine doesn't know how to reference the fields if they are mix matched.

~AB~
0
 
revo1059Author Commented:
They are not the same,  The status in one is 0 and the status in the other is a 1.  This tells me which are received, and which are used
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
revo1059Author Commented:
my mistake, when i pasted, i forgot to change the 0 to a 1.  The used sp should say where status = 1
0
 
revo1059Author Commented:
ok,  I tried what you said about the table alias and it worked.  Now i just need to subtract them using one sp.  Can you help?
0
 
Richard DavisSenior Web DeveloperCommented:
Just create 2 internal variables within the SP and assign a value to them from each select.

e.g. SELECT total1 = sum(qty)..... and the same with the other query...then just do a total1 = total1 - total2 in your SP and make one of the totals an OUT in your SP params.

CREATE PROCEDURE `spTotalReceived`(IN upc BIGINT, OUT total1 INT)

You can find this info available here;
http://dev.mysql.com/doc/refman/5.0/en/call.html
or here
http://www.databasejournal.com/features/mysql/article.php/3525581
0
 
Richard DavisSenior Web DeveloperCommented:
Also...you would want to put both select statements in just one SP to accomplish this.
0
 
revo1059Author Commented:
Here is what i have so far:

DELIMITER $$

DROP PROCEDURE IF EXISTS `inventory`.`spTotal` $$
CREATE PROCEDURE `spTotalUsed`(OUT upc BIGINT)
BEGIN
SELECT sum(p.qty) as 'total used' FROM parts_used p WHERE p.status = 1 and p.upc = upc;
SELECT sum(p.qty) as 'total received' FROM parts_used p WHERE p.status = 0 and p.upc = upc;

END $$

DELIMITER ;

You can see i have both select statements, but I am unclear on how to create variables inside the sp
0
 
revo1059Author Commented:
I made a mistake on the create procedure part, it should say spTotal, not spTotalUsed
0
 
Richard DavisSenior Web DeveloperCommented:
Ok, here's what you would want to do then;

First, change your total received & total used aliases to what I have shown below, because it's not a good idea to use any column names that have spaces in them. As far as the SP itself is concerned, try what I have done below. As you can see, the SP create statement is declaring 2 vars, one for what you're sending into it and 1 that will be the result. Then, you can see that I used a SET statement to do the subtraction and assigned the result to the OUT var. That should be all you need then. :)

DELIMITER $$

DROP PROCEDURE IF EXISTS `inventory`.`spTotal` $$
CREATE PROCEDURE `spTotalUsed`(IN upc BIGINT, OUT amount INT)
BEGIN
SELECT sum(p.qty) as total_used FROM parts_used p WHERE p.status = 1 and p.upc = upc;
SELECT sum(p.qty) as total_rcvd FROM parts_used p WHERE p.status = 0 and p.upc = upc;
SET amount = total_rcvd - total_used
END $$

DELIMITER ;
0
 
revo1059Author Commented:
I tried what you said and got this

Script line: 5      You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET amount = total_rcvd - total_used
END' at line 5
0
 
revo1059Author Commented:
This is exactly what i typed

DELIMITER $$

DROP PROCEDURE IF EXISTS `inventory`.`spTotal` $$
CREATE PROCEDURE `spTotal`(IN upc BIGINT, OUT amount INT)
BEGIN
SELECT sum(p.qty) as total_used FROM parts_used p WHERE p.status = 1 and p.upc = upc;
SELECT sum(p.qty) as total_rcvd FROM parts_used p WHERE p.status = 0 and p.upc = upc;
SET amount = total_rcvd - total_used
END $$

DELIMITER ;
0
 
Richard DavisSenior Web DeveloperCommented:
Ok...sorry, my bad.

Try this perhaps;

DELIMITER $$

DROP PROCEDURE IF EXISTS `inventory`.`spTotal` $$
CREATE PROCEDURE `spTotal`(IN upc BIGINT, OUT amount INT)
BEGIN
DECLARE total_used INT;
DECLARE total_rcvd INT;
SET total_used = (SELECT sum(p.qty) FROM parts_used p WHERE p.status = 1 and p.upc = upc);
SET total_rcvd = (SELECT sum(p.qty) FROM parts_used p WHERE p.status = 0 and p.upc = upc);
SET amount = total_rcvd - total_used;
END $$

DELIMITER ;
0
 
Richard DavisSenior Web DeveloperCommented:
Oh man...I'm dropping the ball all over the place here...forget that last one and try this instead...so sorry;

DELIMITER $$

DROP PROCEDURE IF EXISTS `inventory`.`spTotal` $$
CREATE PROCEDURE `spTotal`(IN upc BIGINT, OUT amount INT)
BEGIN
DECLARE total_used INT;
DECLARE total_rcvd INT;
SELECT sum(p.qty) INTO total_used FROM parts_used p WHERE p.status = 1 and p.upc = upc;
SELECT sum(p.qty) INTO total_rcvd FROM parts_used p WHERE p.status = 0 and p.upc = upc;
SET amount = total_rcvd - total_used;
END $$

DELIMITER ;
0
 
revo1059Author Commented:
Ok, it excepted the sp now, but when i try this

CALL spTotal(780672318895)

I get an error saying

Incorrect number of arguments for PROCEDURE inventory.spTotal; expected 2, got 1

so i tried
CALL spTotal(780672318895, 780672318895)

and that didn't work either

how should i call the sp?
0
 
Richard DavisSenior Web DeveloperCommented:
Ok...my bad...this should do the trick for you.

DELIMITER $$

DROP PROCEDURE IF EXISTS `inventory`.`spTotal` $$
CREATE PROCEDURE `spTotal`(IN upc BIGINT)
BEGIN
DECLARE total_used INT;
DECLARE total_rcvd INT;
SELECT sum(p.qty) INTO total_used FROM parts_used p WHERE p.status = 1 and p.upc = upc;
SELECT sum(p.qty) INTO total_rcvd FROM parts_used p WHERE p.status = 0 and p.upc = upc;
SET amount = total_rcvd - total_used;
END $$

DELIMITER ;

Then when you call your procedure whatever the result is that's stored in the internal variable called 'amount' can be retrieved on the mysql command-line as SELECT @amount;

This should work now...sorry about the mix up, I use MS SQL Server & MySQL and somtimes get these syntaxes crossed.
0
 
revo1059Author Commented:
couple things, my customer has upcs that aren't always numbers.  That was easy to fix, i just changed the IN upc INT TO IN upc VARCHAR(30) that fixed it.  

DELIMITER $$

DROP PROCEDURE IF EXISTS `inventory`.`spTotal` $$
CREATE PROCEDURE `spTotal`(IN upc TEXT)
BEGIN
DECLARE total INT;
DECLARE total_used INT;
DECLARE total_rcvd INT;
SELECT sum(p.qty) INTO total_used FROM parts_used p WHERE p.status = 0 and p.upc = upc;
SELECT sum(p.qty) INTO total_rcvd FROM parts_used p WHERE p.status = 1 and p.upc = upc;
SET total = total_rcvd - total_used;
SELECT total;
END $$

DELIMITER ;

I did some more reading and you have to select the variable after you set it in order for it to be displayed

and i also screwed up and switched the 1 and the 0, but i fixed that.

I am still having a lot of trouble calling the procedure from dreamweaver, but i will give you the points now.  You have been very helpful

ps, if you know how to call it from dreamweaver that would be sweet
0
 
Richard DavisSenior Web DeveloperCommented:
Thanks...I'm afraid I don't know what you mean by calling it from Dreamweaver. Are you using PHP, Coldfusion...what web language are you attempting to call this routine from?
0
 
revo1059Author Commented:
I am using php 5.1 through dreamweaver .  
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now