mysql - multiple select on stored procedure -

i'm trying multiple selects 1 table shown last select statement.

create procedure `usp_getstockcard` (in matecode varchar(10)) begin     (select tran_date  tran_date         tm_matbalance         mate_code=matecode);      (select mate_code mate_code         tm_matbalance         mate_code=matecode);          (select tran_qtyx qty_in          tm_matbalance         tran_type='in'         , mate_code=matecode);      (select tran_qtyx qty_out          tm_matbalance          tran_type='out'         , mate_code=matecode);         end 

i've tried change semicolon comma after each select statement said syntax error: missing 'semicolon'. please help.

i @ problem , think solve it.

basically there 2 problems here first 1 pivot table tran_qtyx column become qty_in , qty_out based on value in tran_type column (in or out)... part of problem solve query

select tran_date, mate_code,        sum(case when tran_type = 'in' tran_qtyx else 0 end) qty_in,        sum(case when tran_type = 'out' tran_qtyx else 0 end) qty_out mytable mate_code = 'mat001' group date(tran_date) 

note: in desired result see 'mat001'as mate_code stick in solution , exclude mat002 result.

more pivot table can read here, there can find link, take look, , can find lot of stuff mysql query's.

the second part of problem qty_balance column. similar problem solved here. it's how calculate row value based on value in previous row.

so complete query this:

select t1.tran_date, t1.mate_code, t1.qty_in, t1.qty_out,         @b := @b + t1.qty_in - t1.qty_out qty_balance (select @b := 0) dummy cross join (select tran_date, mate_code,         sum(case when tran_type = 'in' tran_qtyx else 0 end) qty_in,         sum(case when tran_type = 'out' tran_qtyx else 0 end) qty_out  mytable  mate_code = 'mat001'  group date(tran_date)) t1  order t1.tran_date; 

note: think should change here table name , it's should work.

here sql fiddle can see how that's work!

gl!