|
View previous topic :: View next topic
|
| Author |
Message |
Saikat Sengupta
New User
Joined: 08 Dec 2009
Posts: 15
Location: Mumbai
|
|
|
|
I am facing a problem understanding the out put of a DB2 query. I’ll briefly give u the table columns and then the query.
The table is an Employee table with:
1st Col: Dept Num
2nd Col: Employee ID
3rd Col: Employee Last Name
4th Col: Employee First Name
…. The rest of the cols I am not including since the query is restricted to these columns.
I ran the following query 1st:
| Code: |
Select Dept_Num, Employee_ID, Emp_Lst_Nam, Emp_Fst_Nam
From DXXX.TBWP_EMP
Where Emp_Lst_Nam IN (‘PANCAKE’); |
The output was:
| Code: |
---------+---------+---------+---------+---------+---------+---------+
DEPT_NUM EMPLOYEE_ID EMP_LST_NAM EMP_FST_NAM
---------+---------+---------+---------+---------+---------+---------+
14. 90440. PANCAKE MEREDITH
458. 1641. PANCAKE MARY
DSNE610I NUMBER OF ROWS DISPLAYED IS 2 |
I ran the following query next:
| Code: |
Select Dept_Num, Employee_ID, Emp_Lst_Nam, Emp_Fst_Nam
From DXXX.TBWP_EMP
Where Employee_ID IN ( Select Employee_ID from DXXX.TBWP_EMP
Where Emp_Lst_Nam IN (‘PANCAKE’)) |
The output was:
| Code: |
---------+---------+---------+---------+---------+---------+
DEPT_NUM EMPLOYEE_ID EMP_LST_NAM EMP_FST_NAM
---------+---------+---------+---------+---------+---------+
14. 90440. PANCAKE MEREDITH
458. 1641. PANCAKE MARY
881. 1641. BENTLEY HILMA
3885. 1641. WALLACE SARA
4022. 1641. LEMAHIEU BARBARA
4138. 1641. LOMELI ESTHER
DSNE610I NUMBER OF ROWS DISPLAYED IS 6 |
I am not getting how is the 2nd query working to produce such result set. Can anyone explain how the 2nd query is working?
Coded - Anuj |
|
| Back to top |
|
 |
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
| Perhaps, someone inserted some rows after your first query execution, before second query run. |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| You have multiple employee_id's with different emp_lst_name. employee_id '1641' is used for Bentley, Wallace, Lemahieu, and Lomeli. Your first query is based on emp_lst_name but your second is based on employee_id (which is not unique across dept_num's) |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
your first query returns two EmpIds : 90440 and 1641
your second query returns all rows with those empids
maybe what you wanted to write was :
| Code: |
Select Dept_Num, Employee_ID, Emp_Lst_Nam, Emp_Fst_Nam
From DXXX.TBWP_EMP
Where (dept_num,Employee_ID) IN ( Select Dept_num,Employee_ID from DXXX.TBWP_EMP
Where Emp_Lst_Nam IN (‘PANCAKE’)) |
|
|
| Back to top |
|
|
|
|
