IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Retrieve employees who are working in more than 1 department


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Apr 24, 2007 12:19 pm
Reply with quote

Hi

I have an EMPL table with columns as

EMPNO, EMPNAME, DEPT1, DEPT2, DEPT3

I want to retrieve those employees who are working in more than 1 department.

Thanks
Arun
Back to top
View user's profile Send private message
ashwinreddy

Active User


Joined: 16 Sep 2004
Posts: 106
Location: Hyderabad

PostPosted: Tue Apr 24, 2007 2:29 pm
Reply with quote

Hi,

Their might be many solutions i think one which is easer for me

Select EMPNO,EMPNAME

From EMPL

where (DEPT1 > 0
and DEPT2 > 0)

or


(DEPT2 > 0
and DEPT3 > 0)

or

(DEPT3 > 0
and DEPT1 > 0)

i have given a solution without testing, you have to test it.

And more optimised quires will be posted by other members.

Cheers
Ashwin kumar
Back to top
View user's profile Send private message
mahi

New User


Joined: 04 Apr 2006
Posts: 86
Location: Hyderabad

PostPosted: Tue Apr 24, 2007 3:54 pm
Reply with quote

Hi Ashwin,
I think it won't work....

Arun pls go through this link
www.ibmmainframes.com/viewtopic.php?t=14746&highlight=
Back to top
View user's profile Send private message
vicky10001
Warnings : 1

Active User


Joined: 13 Jul 2005
Posts: 136

PostPosted: Tue Apr 24, 2007 4:35 pm
Reply with quote

Dear All,

Please give proper answer bocoz its important.
Pleas find the above two answer was wrong.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Apr 24, 2007 5:49 pm
Reply with quote

Hi mahi

Yours ws a different scenario where you were having only two columns EMPNO and DEPT.But in my case it is not so.

Thanks
Arun
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Tue Apr 24, 2007 8:47 pm
Reply with quote

Hello,

The previous dialog has me confused icon_confused.gif

If i read this
Quote:
I have an EMPL table with columns as
EMPNO, EMPNAME, DEPT1, DEPT2, DEPT3
I want to retrieve those employees who are working in more than 1 department.
correctly, Arun wants a return of those who have more than 1 dept "active" (i.e. works in more than 1 dept - the code has a "real" value).

Ashwin's suggestion (i've re-structured it to make it take less space)
Code:
Select EMPNO,EMPNAME
From EMPL
where (DEPT1 > 0 and DEPT2 > 0)
   or (DEPT2 > 0 and DEPT3 > 0)
   or (DEPT3 > 0 and DEPT1 > 0)

looks ok as long as dept is numeric - if it is alpha, the test could be for not= spaces rather than >zero.

Please let me know if i've misunderstood. . .
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Apr 25, 2007 11:08 am
Reply with quote

Hi all,

Thanks for the response

Arun
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Wed Apr 25, 2007 11:35 pm
Reply with quote

Hello Arun,

You're welcome icon_smile.gif

But. . . . did you get an answer that works for you?

If you did, and post it, it may help someone later with a similar question.
Back to top
View user's profile Send private message
Raphael Bacay

New User


Joined: 04 May 2007
Posts: 58
Location: Manila, Philippines

PostPosted: Fri May 18, 2007 1:23 pm
Reply with quote

Hi!

Maybe instead of 0 you can use null. The SELECT will look something like this:


SELECT EMPNO, EMPNAME
FROM EMP
WHERE (DEPT1 NOT NULL AND
DEPT2 NOT NULL) OR
(DEPT2 NOT NULL AND
DEPT 3 NOT NULL) OR
(DEPT1 NOT NULL AND
DEPT3 NOT NULL)

Hope that is helpful.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Fri May 18, 2007 7:40 pm
Reply with quote

Hello,

So long as DEPTn is defined to support NULL. . . .
Back to top
View user's profile Send private message
SHAILESH OZA

New User


Joined: 10 Jun 2005
Posts: 21
Location: Mumbai

PostPosted: Tue Jun 26, 2007 5:01 pm
Reply with quote

one more thing you can do is . since you are having three department says COMP ,EXTC AND MECH. SO you can apply the following query also.

SELECT EMPNAME FROM EMP
where (DEPT1 = 'COMP' and DEPT2 = 'EXTC')
or (DEPT2 = 'EXTC' and DEPT3 = 'MECH')
or (DEPT3 = 'MECH' and DEPT1 = 'COMP')
or( DEPT1 = 'COMP' and DEPT2 = 'EXTC' and DEPT3 = 'MECH')
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Tue Jun 26, 2007 7:58 pm
Reply with quote

Hello,

You will not want to hard-code specific values. That is (IMHO) a bad practice. Think of the nightmare if there were 200 departments at the company. . .

The only requirement was to identify those with more than 1 dept worked. . .
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 148
Location: Ottawa Canada

PostPosted: Tue Jun 26, 2007 9:48 pm
Reply with quote

Your EMPLOYEE table is not normalized.

There should only be 1 instance of DEPT on a single row.

If you are responsible for the table then consider normalizing it. If not responsible, ask the DBA why it is not normalized.
Back to top
View user's profile Send private message
Bitneuker

CICS Moderator


Joined: 07 Nov 2005
Posts: 1104
Location: The Netherlands at Hole 19

PostPosted: Wed Jun 27, 2007 2:39 am
Reply with quote

TG Murphy wrote:
Your EMPLOYEE table is not normalized.

There should only be 1 instance of DEPT on a single row.

If you are responsible for the table then consider normalizing it. If not responsible, ask the DBA why it is not normalized.


Fully agreed icon_exclaim.gif

We are looking an n:m relationship. Entity employee is one and department is the other. In this relationship between entities an employee can work in 1:n departments and, in a departement 1:n employees can work. This causes the n:m relationship. The solution is a relation-entity between both. We call this data modelling (TG understands this) including normalisation towards the 5th form. You will probably end up with an extra table containing the keys for employees and departments.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts PD not working for unsigned packed JO... DFSORT/ICETOOL 5
No new posts Def PD not working for unsigned packe... JCL & VSAM 3
No new posts Using Java/C/C++ to retrieve dataset ... Java & MQSeries 6
No new posts ICETOOL with JOINKEY for Big record l... DFSORT/ICETOOL 12
No new posts Retrieve IMS SubSystem Name IMS DB/DC 2
Search our Forums:

Back to Top