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
 

 

Help needed with SELECT

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
veeramanimurugesan

New User


Joined: 03 Nov 2009
Posts: 19
Location: chennai

PostPosted: Sat May 21, 2011 5:34 pm    Post subject: Help needed with SELECT
Reply with quote

Hi,

Need some help with SELECT clause.

EMP table has a field employee id which is char 7. The first 3 bytes of employee id is branch number and the next 4 is a unique number for that employee.

I have to pick up 1 employee in each branch from this table. This employee id is the key of table.

Table data:
EMP ID
AAA1234
AAA1222
AAA5678
BBB1234
CCC1234
CCC5678

I want my query result to be:
AAA1234
BBB1234
CCC1234

I tried using SELECT DISTINCT(SUBSTR(EMP_ID,1,3)) but this gives me only unique branch number in the output like AAA,BBB,CCC whereas i need the full 7 bytes. I tried to concat this with EMP_ID,4,4 but then it would pick up all employees.

Any employee within a branch could be picked up in random and there is no rule for that.

Please advise.
Back to top
View user's profile Send private message

Pjoe

New User


Joined: 22 Jul 2005
Posts: 17

PostPosted: Sat May 21, 2011 9:33 pm    Post subject: Reply to: Help needed with SELECT
Reply with quote

You may use this query..

select max (emp_number) from yourdb.yourtable group by substr(emp_number,1,3);
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: Sun May 22, 2011 2:19 am    Post subject:
Reply with quote

Hello,

Was this tested before posting? It looks as though it will not produce the wanted results. . .

Veera,
How were the "wanted" results determined? While AAA1234 is first in your "input list" there is no way to guarantee this sequence. . .
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Sun May 22, 2011 3:10 am    Post subject: Reply to: Help needed with SELECT
Reply with quote

IT as a discipline where processes are usually deterministic and repeatable

Quote:
Any employee within a branch could be picked up in random and there is no rule for that.

the most silly requirement
when You run a query You must set some rules and RANDOM is not one of them

we might argue for ages whether the a MIN or MAX or FIRST/LAST in an unordered select might fit

so it would be better to come up with a better/more sensible requirement

/humor on
and ... just curious, what kind of processing is going to be done on this random employee ?
give him/her an award,
give him/her a raise,
relocate him/her,
tell him/her that the next assignment will be to replace the janitor
terminate him/her,
give him/her an attaboy/attagirl ...

oops I forgot ... for a random employee take a random action icon_biggrin.gif

/humor off

really some teachers in training institutes are really in need of training themselves icon_eek.gif

and please do not tell that this is a business requirement, nobody will believe You icon_cool.gif
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Sun May 22, 2011 3:15 am    Post subject: Reply to: Help needed with SELECT
Reply with quote

follow on

from Your initial post

Quote:
Table data:
EMP ID
AAA1234
AAA1222
AAA5678
BBB1234
CCC1234
CCC5678

I want my query result to be:
AAA1234
BBB1234
CCC1234


what if the query returned instead

AAA1222
BBB1234
CCC5678

the randomness requirement would be satisfied, but it will not agree with the sentence
Quote:
I want my query result to be:
AAA1234
BBB1234
CCC1234


and what if You' d get Your sample the first time and my sample the second time , something else the third time ???

better learn how to ask questions in a better way !
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: Sun May 22, 2011 6:00 am    Post subject:
Reply with quote

Hello,

Quote:
IT as a discipline where processes are usually deterministic and repeatable


If the result is NOT repeatable (i.e. one query, different answers) i suspect some manager or user will not find this acceptable. . .

Even if some kind of "random answer" was somehow acceptable, from what i see here, the "answer" will be the same for a while and then mysteriously change causing considerable confusion. . . icon_confused.gif
Back to top
View user's profile Send private message
veeramanimurugesan

New User


Joined: 03 Nov 2009
Posts: 19
Location: chennai

PostPosted: Sun May 22, 2011 1:49 pm    Post subject:
Reply with quote

Thanks experts for the humorous responses!

This ain't a training assignment at first. I just wanted to keep the question generalized and not go into the business part of it to avoid confusions.

Ok this is the table which holds the details of branch managers. So there could be possibilities that 1 branch could have 2 managers or more which is a valid business case. So the output of my current query was fetching 2 branch managers employee id's for some specific branches(AAA1234,AAA5678). But this was causing duplicate rows to be put on some other tables down my job stream.

So i have to pick up only 1 branch manager now. This is just for the case of contacting the branch. So it really does not matter which manager among the managers you contact! To pick up the same manager among the multiple managers we can sequence the employee id on asc or desc etc

Hope my requirement is now clear.. can you please help now?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Sun May 22, 2011 2:25 pm    Post subject: Reply to: Help needed with SELECT
Reply with quote

/HUMOR OFF

Quote:
Thanks experts for the humorous responses!

horse manure... the responses were not humorous
just the expression of serious concerns with the stupidity (*) of the request and Your poor understanding of general programming logi

and if this is a real business requirement i would even more concerned with the stupidity(*) of the business requirement

(*) whether You like or not

everywhere even the contacts are prioritized according to some <rank> in the organization
even if in the <branch> there are two or more <manglers> one will be somehow have a higher rank
shared command is one of the worst plagues of organizations

nuff said...

Quote:
Hope my requirement is now clear.. can you please help now?

nope, Your requirement is still clear as mud

given multiple occurrences of something it' s completely up to You, to define an algorithm/criteria for the <right> choice

if You keep on with such silliness the topic will be locked shortly

as already suggested chose min/max according to some column/token that can be ordered/ranked
and if You cannot write a select with a MIN/MAX ....
a suggestion about getting proper/better training stands more than appropriate
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: Mon May 23, 2011 1:14 am    Post subject:
Reply with quote

Hello,

While there might be multiple managers in a department (0 - n), there should also be a designated "contact" for that department. . .

The query should select the contact for each department rather than some manager .
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon May 23, 2011 1:22 am    Post subject: Reply to: Help needed with SELECT
Reply with quote

Quote:
The query should select the contact for each department rather than some manager .


from the flow of the discussion HR does not care who is the contact
that' s why the TS asked how to get a random occurrences for a query that returns multiple rows icon_evil.gif
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: Mon May 23, 2011 1:38 am    Post subject: Reply to: Help needed with SELECT
Reply with quote

Hi Enrico,

Can you imagine the scene when "Fred" is "contacted" one day after "Mary" has been receiving these for a few years. . . icon_cool.gif

I suppose that if i was given such a "requirement" (any manager will do), i'd look to select the manager with the longest time being a manager in the department. Not sure who i'd contact if the department was between managers. . .

d
Back to top
View user's profile Send private message
murugan_mf

Active User


Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India

PostPosted: Thu Jun 02, 2011 1:27 pm    Post subject:
Reply with quote

I am not sure whether this is an optimized one..

Code:
SELECT SUBSTR(A.EMP_ID,1,3),A.EMP_ID         
FROM EMPTABL AS A                                         
GROUP BY SUBSTR(A.EMP_ID,1,3),A.EMP_ID         
HAVING SUBSTR(A.EMP_ID,4,4) =                           
(                                                               
SELECT MIN(SUBSTR(EMP_ID,4,4))                         
FROM EMPTABL WHERE                                         
SUBSTR(EMP_ID,1,3) = SUBSTR(A.EMP_ID,1,3)     
);
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Tue Jun 07, 2011 11:47 am    Post subject:
Reply with quote

Hi Veera,

Please try below query

Code:
SELECT MAX(A.EMP_ID)           AS  EMPID
FROM    EMPTABL  A
GROUP BY SUBSTR(A.EMP_ID,1,3)


Here you can change MAX(A.EMP_ID) to MIN(A.EMP_ID) as per your convenience
I hope this helps.

Regards,
Chandan
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Tue Jun 07, 2011 12:35 pm    Post subject:
Reply with quote

@chandan.inst
Did you test your code for the above shown input and output?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    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 Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Help Needed with View Direct shailesh_do CA Products 2 Wed Jul 13, 2016 10:39 am
No new posts Select first 2 group data vice_versa DFSORT/ICETOOL 10 Mon Jun 13, 2016 2:21 pm
No new posts Query to select Null row on priority RahulG31 DB2 24 Thu Jan 21, 2016 5:45 pm
No new posts Can I use sort to select sample recor... Steve Ironmonger DFSORT/ICETOOL 3 Tue Nov 10, 2015 10:02 pm


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