multiple select on stored procedure

Question

I'm trying to do multiple selects from one table but it only shown the last select statement.

CREATE PROCEDURE `usp_GetStockCard` (IN Matecode varchar(10))
BEGIN
    (select tran_date  as tran_date
        from TM_matbalance
        where Mate_code=Matecode);

    (select Mate_code as Mate_code
        from TM_matbalance
        where Mate_code=Matecode);    

    (select tran_qtyx as Qty_in 
        from TM_matbalance
        where tran_type='IN'
        and mate_code=matecode);

    (select tran_qtyx as Qty_out 
        from TM_matbalance
        where  tran_type='OUT'
        and mate_code=matecode);    
    END

I've tried to change semicolon to comma after each select statement but it said that syntax error: missing 'semicolon'. please help.

here some data and desired output: [link](http://tinypic.com/r/2zxs8qe/8) — Syns, Jun 26 '15 at 07:10

score 0 · Answer 1 · edited Jun 26 '15 at 13:21

0

You will need to structure your query into one, or pass in a parameter to the stored procedure to select which output/query you want, to restructure your query you will need something like:

`CREATE PROCEDURE `usp_GetStockCard` (IN Matecode varchar(10))
BEGIN
(select tran_date  as tran_date, Mate_code as Mate_code, tran_qtyx as Qty
    from TM_matbalance
    where Mate_code=Matecode
    and (tran_type='IN' or tran_type='OUT');
END`

Or try this if you have an ID column:

SELECT coalesce(ta.id, tb.id) as tran_id, coalesce(ta.tran_date, tb.tran_date)  as tran_date, coalesce(ta.Mate_code, tb.Mate_code)  as Mate_code, ta.tran_type as Qty_In, tb.tran_type as Qty_Out
from (select ta.*
  from TM_matbalance ta
  where ta.tran_type = 'IN'
and Mate_code=Matecode
 ) ta full outer join
 (select tb.*
  from TM_matbalance tb
  where tb.tran_type = 'OUT'
and Mate_code=Matecode
 ) tb
 on ta.id = tb.id ;

just replace "id" with the name of your ID column if you don't need to return the id column then remove coalesce(ta.id, tb.id) as tran_id

edited Jun 26 '15 at 13:21

Galma88

2,398
6
29
50

answered Jun 25 '15 at 08:18

Dave

967
10
23

wow thank you, the statement is working but how to separate tran_qtyx to qty_in and qty_out? so the result would be "tran_date, mate_code, qty_in,qty_out" I've tried adding another 'select' statement but it doesn't work. – Syns Jun 26 '15 at 07:23
Is there no way you can handle this in your coding environment? – Dave Jun 26 '15 at 08:59
Like if (Qty == "IN"){Do something} else if (Qty == "OUT"){Do something else} else {Do this} – Dave Jun 26 '15 at 09:02
What language are you using to process your query? Maybe I can help you with that? – Dave Jun 26 '15 at 09:09
sorry for being silly I thought I solve it, here's the table [expected result] (http://tinypic.com/r/205e1xt/8) – Syns Jun 26 '15 at 09:33
Do you have to do it using SQL? – Dave Jun 26 '15 at 10:22
Do you have an ID column for the table? then we can do a full outer join – Dave Jun 26 '15 at 13:05
I added in a new query, it should work but you might need to fiddle with it a little – Dave Jun 26 '15 at 13:17
thank's Dave, but this 'ta full outer join' 'ta' is always expecting semicolon, while 'full' expecting colon. – Syns Jun 27 '15 at 03:09

score 0 · Accepted Answer · edited May 23 '17 at 12:17

I look at your problem and I think I solve it.

Basically there is two problems here first one is to pivot your table where your Tran_Qtyx column become Qty_In and Qty_Out based on value in Tran_Type column (IN or OUT)... That part of problem you solve with this query

SELECT Tran_Date, Mate_Code,
       SUM(CASE WHEN Tran_Type = 'IN' THEN Tran_Qtyx ELSE 0 END) Qty_In,
       SUM(CASE WHEN Tran_Type = 'OUT' THEN Tran_Qtyx ELSE 0 END) Qty_Out
FROM myTable
WHERE Mate_Code = 'MAT001'
GROUP BY DATE(Tran_Date)

NOTE: In your desired result I only see 'MAT001'as Mate_Code so I stick with that in this solution and exclude MAT002 from result.

More about pivot table you can read here, there you can find a link, which is good to take a look, and where you can find a lot of stuff about mysql query's.

The second part of your problem is to get Qty_Balance column. Similar problem is solved here. It's how to calculate row value based on the value in previous row.

So your complete query could look like this:

SELECT t1.Tran_Date, t1.Mate_Code, t1.Qty_In, t1.Qty_Out, 
       @b := @b + t1.Qty_In - t1.Qty_Out AS Qty_Balance
FROM
(SELECT @b := 0) AS dummy
CROSS JOIN
(SELECT Tran_Date, Mate_Code,
        SUM(CASE WHEN Tran_Type = 'IN' THEN Tran_Qtyx ELSE 0 END) Qty_In,
        SUM(CASE WHEN Tran_Type = 'OUT' THEN Tran_Qtyx ELSE 0 END) Qty_Out
 FROM myTable
 WHERE Mate_Code = 'MAT001'
 GROUP BY DATE(Tran_Date)) AS t1
 ORDER BY t1.Tran_Date;

NOTE: probably only think you should change here is table name and it's should work.

Here is SQL Fiddle so you can see how that's work!

GL!

yeaa it works, I never thought of using pivot before guess I'll need to learn harder. thank you sir. — Syns, Jun 27 '15 at 03:17

multiple select on stored procedure

2 Answers2