Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need a query to display employees worked at multiple branch

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

New User


Joined: 09 May 2005
Posts: 18

PostPosted: Tue May 03, 2011 5:15 pm    Post subject: Need a query to display employees worked at multiple branch
Reply with quote

I am having a table with hundreds of rows shown below

EmpNo Branch
------ ---------
1234 ABC123
1234 BCD123
1234 CDE123
2345 ABC123
3456 ABC123
4567 ABC123
4567 CDE123
5678 BCD123
5678 DEF123


I need a query to display only the employee rows who worked in multiple
branches. See below

EmpNo Branch
------ ---------
1234 ABC123
1234 BCD123
1234 CDE123
4567 ABC123
4567 CDE123
5678 BCD123
5678 DEF123

I dont want to disply the rows

2345 ABC123
3456 ABC123

Can some one please give me the query.
Back to top
View user's profile Send private message

prino

Senior Member


Joined: 07 Feb 2009
Posts: 1072
Location: Oostende, Belgium

PostPosted: Tue May 03, 2011 5:23 pm    Post subject: Re: Need a query to display employees worked at multiple bra
Reply with quote

komal wrote:
I am having a table with hundreds of rows shown below

EmpNo Branch
------ ---------
1234 ABC123
1234 BCD123
1234 CDE123
2345 ABC123
3456 ABC123
4567 ABC123
4567 CDE123
5678 BCD123
5678 DEF123


I need a query to display only the employee rows who worked in multiple
branches. See below

EmpNo Branch
------ ---------
1234 ABC123
1234 BCD123
1234 CDE123
4567 ABC123
4567 CDE123
5678 BCD123
5678 DEF123

I dont want to disply the rows

2345 ABC123
3456 ABC123

Can some one please give me the query.

Just transfer EUR 700 to my PayPal account, and you'll get your query.

THIS IS A FLUCKINGH HELP FORUM, NOT A DO MY WORK FOR ME ONE!
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1532
Location: Chennai

PostPosted: Tue May 03, 2011 5:39 pm    Post subject:
Reply with quote

Logic:
Use a count operator on EMPNO,
use a group by on empno having count > 1
Use order by EMPNO,BRANCH.

Writing the query is upto you.

Hope it helps.
(Tested on SAS PROC SQL)
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10373
Location: italy

PostPosted: Tue May 03, 2011 5:43 pm    Post subject: Reply to: Need a query to display employees worked at multip
Reply with quote

Your first post dealing with db2/sql was in 2006 after five years You should have learned how to search and how to ask questions
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue May 03, 2011 9:17 pm    Post subject:
Reply with quote

I admit HAVING is a basic SQL-element that everyone should know, but one good example is all the poor TS needs.
Instead of feeling superior it would be better to just help the guy.

Code:
SELECT COL1 from tab1 GROUP BY COL1 HAVING COUNT(*) > 1

would give you all the COL1 with multiple rows in the table.
It won't give you all the rows, just all the distinct COL1 values .
If you actually need all the corresponding rows, you would need to join this result with the original table.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Wed May 04, 2011 4:50 pm    Post subject:
Reply with quote

GuyC wrote:
Instead of feeling superior it would be better to just help the guy.
Like! icon_smile.gif
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 query to fetch record which has only ... maxsubrat DB2 2 Mon Dec 11, 2017 5:03 pm
No new posts Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 1 Wed Dec 06, 2017 1:50 am
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm
No new posts Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 4 Tue Nov 07, 2017 8:34 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us