OraFAQ Forum: SQL & PL/SQL » sql query problem

Home » SQL & PL/SQL » SQL & PL/SQL » sql query problem (windows xp, oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql query problem [message #604401]

Mon, 30 December 2013 03:39

sudeshna_bio08
Messages: 11
Registered: December 2013
Location: india

Junior Member

Dear all,

I am using Oracle db 11g. My table t having data like:

empid leave_id status master_id
GHE0000484 LVRL00002 Y ELVM000015
GHE0000005 LVRL00001 Y ELVM000001
GHE0000484 LVRL00001 Y ELVM000014
GHE0000489 LVRL00001 Y ELVM000022

where leave id LVRL00002 stand for CL and LVRL00001 for EL.
I need a query to calculate number of cl and el of employees.

select b.employee_id,nvl(decode(b.leave_rule_id, 'LVRL00001', COUNT(b.employee_id)), 0) "CL",
nvl(decode(b.leave_rule_id, 'LVRL00002', COUNT(b.employee_id)), 0) "EL"
from emp_leave_master b
group by b.employee_id,b.leave_rule_id

This query diplays the desired output.But,I dont want to mention the leaveid directly in decode function of sql query.
Is there any way to display the same output without directly giving leave id??

Report message to a moderator

Re: sql query problem [message #604402 is a reply to message #604401]

Mon, 30 December 2013 03:59

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

leave id LVRL00002 stand for CL and LVRL00001 for EL.

What does this mean?

Quote:

This query diplays the desired output.But,I dont want to mention the leaveid directly in decode function of sql query.

Why? And how could you do if this is this column value that determines the group?

Quote:

Is there any way to display the same output without directly giving leave id??

Once again this is not clear.

Maybe you should give us the result you want for the data you gave.
As far as I understand it might something like:

select b.employee_id,
       count(decode(b.leave_rule_id, 'LVRL00001', 'CL') "CL",
       count(decode(b.leave_rule_id, 'LVRL00002', 'EL') "EL"
from emp_leave_master b
group by b.employee_id
/

Report message to a moderator

Re: sql query problem [message #604405 is a reply to message #604402]

Mon, 30 December 2013 04:20

sudeshna_bio08
Messages: 11
Registered: December 2013
Location: india

Junior Member

Dear sir,

Actually I am creating a view based on the table so that if HR department want to know that any
employee applied for casual or earned leave out of actual number of working day they can get
the result from the view.

My base table emp_leave_master data:

employee_id      leave id    approved?  master_id      
GHE0000484	LVRL00002	Y	ELVM000015
GHE0000005	LVRL00001	Y	ELVM000001
GHE0000484	LVRL00001	Y	ELVM000014
GHE0000489	LVRL00001	Y	ELVM000022

here leave id LVRL00001 is for casual leave type and LVRL00002 is for earned leave type.

I have created a view based on the below mentioned query:

create view emp_leave_vu as
select b.employee_id,nvl(decode(b.leave_rule_id,   'LVRL00001',   COUNT(b.employee_id)),   0) "CL",
  nvl(decode(b.leave_rule_id,   'LVRL00002',   COUNT(b.employee_id)),   0) "EL"
  from emp_leave_master b
group by  b.employee_id,b.leave_rule_id

output:

employee_id    cl      el
GHE0000005	1	0
GHE0000484	1	0
GHE0000489	0	1

This result is correct.But If any other developer is adding another leave type in
table emp_leave_master(suppose leave type X, leave id: LVRL00004) then again I have to change my
select query underlying emp_leave_vu view as I have mentioned leave id directly in the decode
function of select statement.Is there any other way to write the select query
without hard coding the leave id so that although a new leave id added in master table
i donot need to change the view??

Report message to a moderator

Re: sql query problem [message #604407 is a reply to message #604405]

Mon, 30 December 2013 05:11

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Is there any other way to write the select query
without hard coding the leave id so that although a new leave id added in master table
i donot need to change the view??

How do you want to add a new column in the result without rewriting the query?

In addition, say your query is correct, I don't think it is.

Report message to a moderator

Re: sql query problem [message #604410 is a reply to message #604405]

Mon, 30 December 2013 06:09

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

sudeshna_bio08 wrote on Mon, 30 December 2013 15:50

But If any other developer is adding another leave type in
table emp_leave_master(suppose leave type X, leave id: LVRL00004) then again I have to change my
select query underlying emp_leave_vu view as I have mentioned leave id directly in the decode
function of select statement.Is there any other way to write the select query
without hard coding the leave id so that although a new leave id added in master table
i donot need to change the view??

That's possible if you maintain another table as LEAVE_RULE_ID and join the master table and leave rule table. Create required primary key and foreign key.

It is possible to avoid hard-coding the leave type if you maintain a table for leave type, and join it with master table. However, you cannot have column wise distribution of leave types, since your columns are not static. One way to do is Dynamic SQL which I personally won't like to do.

This is one way to avoid hard coding :

SQL> DROP TABLE t_emp_master;
 
Table dropped
SQL> CREATE TABLE t_emp_master(employee_id  VARCHAR2(10),    leave_id VARCHAR2(10),    approved VARCHAR2(1),  master_id VARCHAR2(10));
 
Table created
SQL> INSERT INTO t_emp_master values('GHE0000484', 'LVRL00002',   'Y',   'ELVM000015');
 
1 row inserted
SQL> INSERT INTO t_emp_master values('GHE0000005', 'LVRL00001',   'Y',   'ELVM000001');
 
1 row inserted
SQL> INSERT INTO t_emp_master values('GHE0000484', 'LVRL00001',   'Y',   'ELVM000014');
 
1 row inserted
SQL> INSERT INTO t_emp_master values('GHE0000489', 'LVRL00001',   'Y',   'ELVM000022');
 
1 row inserted
SQL> INSERT INTO t_emp_master values('GHE0000489', 'LVRL00001',   'Y',   'ELVM000022');
 
1 row inserted
SQL> COMMIT;
 
Commit complete
SQL> DROP TABLE t_leave_rule;
 
Table dropped
SQL> CREATE TABLE t_leave_rule(leave_id VARCHAR2(10), leave_type VARCHAR2(2), leave_desc VARCHAR2(100));
 
Table created
SQL> INSERT  INTO t_leave_rule VALUEs('LVRL00001', 'CL', 'Casual leave');
 
1 row inserted
SQL> INSERT  INTO t_leave_rule VALUEs('LVRL00002', 'EL', 'Earned leave');
 
1 row inserted
SQL> INSERT  INTO t_leave_rule VALUEs('LVRL00003', 'PL', 'Personal leave');
 
1 row inserted
SQL> COMMIT;
 
Commit complete
SQL> SELECT employee_id, leave_type, COUNT(lr.leave_id) Total FROM t_emp_master em, t_leave_rule lr
  2  WHERE em.leave_id=lr.leave_id
  3  AND em.approved='Y'
  4  GROUP BY employee_id, leave_type,lr.leave_id
  5  order BY employee_id;
 
EMPLOYEE_ID LEAVE_TYPE      TOTAL
----------- ---------- ----------
GHE0000005  CL                  1
GHE0000484  CL                  1
GHE0000484  EL                  1
GHE0000489  CL                  2

Now, you could use PIVOT to make the leave type as columns. However, unfortunately, even PIVOT needs the static leave types. So, even PIVOT would not work for your requirements.

[Updated on: Sat, 08 March 2014 13:54] by Moderator

Report message to a moderator

Re: sql query problem [message #604411 is a reply to message #604410]

Mon, 30 December 2013 06:25

sudeshna_bio08
Messages: 11
Registered: December 2013
Location: india

Junior Member

Thanx for your suggestion Smile

Regards
Sudeshna

Report message to a moderator

Previous Topic:	"with" clause Vs. multiple views
Next Topic:	insert query getting values using select getting next id logic

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 15:35:02 CDT 2024