Dev Research Dev Research - 2 months ago 6
MySQL Question

how to select value in mySql

CREATE DEFINER=`root`@`localhost` PROCEDURE `My_PRO`(in employeId int)
BEGIN
declare allocatedvalue1 int default 0;
declare allocatedvalue2 int default 0;
declare appliedvalue1 int default 0;
declare appliedvalue2 int default 0;

set allocatedvalue1=(
SELECT sum(day) FROM Parabola.leave_allocated where employee_id=741 and
leave_type_id=42 and year='2016') ;

set appliedvalue1=(
select IFNULL(FORMAT(sum(hours/8),1),0)from leave_applied where
employee_id=741 and leave_type_id in(42) and status in (1,2,5)and
(start_date>='2016-01-01'
and end_date<='2016-12-31' ));

set allocatedvalue2=(

select sum(day) from compensatory_leave where
employee_id=741 and status in(2,3));

set appliedvalue2=(
select sum(day) from compensatory_leave where
employee_id=741);
END


This is my Procedure i want to display value like this

**label applied**

FirstLeave allocatedvalue1/appliedvalue1
SecondLeave allocatedvalue2/ appliedvalue2


Please suggest my how to write inside procdure so that i can get value like given structure i am able to get value in all allocatedvalue1,appliedvalue1 ... but i am unable to select value so that i can get desire value.

Answer

Take a look at this:

select 'FirstLeave' as FirstLeave,allocatedvalue1/appliedvalue1 as fvalue1

union all

select  'SecondLeave' as   SecondLeave,      allocatedvalue2/ appliedvalue2 as fvalue2