Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Retrieve employees who are working in more than 1 department

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Arun Raj

Moderator


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

PostPosted: Tue Apr 24, 2007 12:19 pm    Post subject: Retrieve employees who are working in more than 1 department
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    Post subject:
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: Pune

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

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

Arun pls go through this link
http://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    Post subject:
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: 2275
Location: @my desk

PostPosted: Tue Apr 24, 2007 5:49 pm    Post subject:
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

Site Director


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

PostPosted: Tue Apr 24, 2007 8:47 pm    Post subject:
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: 2275
Location: @my desk

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

Hi all,

Thanks for the response

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

Site Director


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

PostPosted: Wed Apr 25, 2007 11:35 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Fri May 18, 2007 7:40 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Tue Jun 26, 2007 7:58 pm    Post subject:
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: 149
Location: Ottawa Canada

PostPosted: Tue Jun 26, 2007 9:48 pm    Post subject:
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: 1105
Location: The Netherlands at Hole 19

PostPosted: Wed Jun 27, 2007 2:39 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts SCRNAME not working on my mainframe? mrsidhu12 TSO/ISPF 7 Thu Sep 01, 2016 2:17 pm
No new posts Need to retrieve Julian_date Suganya87 DB2 4 Wed Aug 17, 2016 7:27 pm
No new posts 2 same CLIST pgm but with different n... boyti ko CLIST & REXX 5 Fri Jun 03, 2016 4:55 pm
No new posts REXX Screen not working due to LINKED... sundarkudos CLIST & REXX 1 Mon May 09, 2016 1:44 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us