Previous Current Next Record
1) I have emp table with empno(pk), ename, sal and deptno(fk) columns
2) I have following query:
SELECT deptno, empno, ename FROM emp
WHERE sal >= 5000
ORDER BY deptno ASC, empno ASC;
3) Now I will feed an existing value to empno and I want in above result data based on ORDER BY deptno, empno record above this and below this if it is a record in the middle. If it is first record, then that and next record. If it is last record, then that and previous record.
4) ROW_NUMBER is not supported in our DB2 database iseries V5R3. Hence I need a generic solution which will work on all RDBMS.
5) It all hinges around the need to find whether next/previous to current deptno is same one or different one since for same deptno empno will be sorted in ASC order but across deptno's sorting order is not at all guaranteed.
6) If it was only ORDER BY empno ASC, using common table expression with WITH clause, empno = ? OR empno = (SELECT MIN(empno) FROM cte WHERE empno > ?) OR empno = (SELECT MAX(empno) FROM cte WHERE empno < ?) will do the trick but due to ORDER BY deptno, empno, the issue has become complex.
7) You can give solution in one query or more i.e. step wise.
2) I have following query:
SELECT deptno, empno, ename FROM emp
WHERE sal >= 5000
ORDER BY deptno ASC, empno ASC;
3) Now I will feed an existing value to empno and I want in above result data based on ORDER BY deptno, empno record above this and below this if it is a record in the middle. If it is first record, then that and next record. If it is last record, then that and previous record.
4) ROW_NUMBER is not supported in our DB2 database iseries V5R3. Hence I need a generic solution which will work on all RDBMS.
5) It all hinges around the need to find whether next/previous to current deptno is same one or different one since for same deptno empno will be sorted in ASC order but across deptno's sorting order is not at all guaranteed.
6) If it was only ORDER BY empno ASC, using common table expression with WITH clause, empno = ? OR empno = (SELECT MIN(empno) FROM cte WHERE empno > ?) OR empno = (SELECT MAX(empno) FROM cte WHERE empno < ?) will do the trick but due to ORDER BY deptno, empno, the issue has become complex.
7) You can give solution in one query or more i.e. step wise.
Understanding that I don't have DB2 to run against, I believe the query in the code snippet below should work. (Solution relies on subselects, so the database needs to support that; hopefully your version of DB2 does.) I did try this solution (with appropriate replacements) against my PostgreSQL database and it worked as I expected.
As you can see, there are three queries that are then UNION'ed together. All assume that "_value_" is replaced with the specific 'empno' as you described above. The middle query is simply returning the values for that specified 'empno'.
For getting the previous (potential) empno within a department, we are really looking for the maximum 'empno' which is less than the specified one, limiting to the same 'deptno' as the specified 'empno' _and_ having a 'sal' greater than or equal to 5000. Once we have that 'empno' we just request that record and get all the desired columns.
Similarly, for the next (potential) empno within a department, we are really looking for the minimum 'empno' which is greater than the specified one, with the same limits as above. Again, once we have that 'empno' we just request that record and get the desired columns.
With all three retrieved, we UNION so that they are one table of results.
Note that this solution should also automatically handle the case where the specified 'empno' is the lowest or highest within the department; the first subselect will not find any minimum or maximum value, because there will be no records that exceed the value. So the query would be like "empno in ()", which means no record is returned for that one, but the other two queries do return something and thus you get the 2 desired records.
(Of course, if the database does not support subselects, then one just has to break the subselects into individually run queries, saving the results to then manually input into the next query.)
As you can see, there are three queries that are then UNION'ed together. All assume that "_value_" is replaced with the specific 'empno' as you described above. The middle query is simply returning the values for that specified 'empno'.
For getting the previous (potential) empno within a department, we are really looking for the maximum 'empno' which is less than the specified one, limiting to the same 'deptno' as the specified 'empno' _and_ having a 'sal' greater than or equal to 5000. Once we have that 'empno' we just request that record and get all the desired columns.
Similarly, for the next (potential) empno within a department, we are really looking for the minimum 'empno' which is greater than the specified one, with the same limits as above. Again, once we have that 'empno' we just request that record and get the desired columns.
With all three retrieved, we UNION so that they are one table of results.
Note that this solution should also automatically handle the case where the specified 'empno' is the lowest or highest within the department; the first subselect will not find any minimum or maximum value, because there will be no records that exceed the value. So the query would be like "empno in ()", which means no record is returned for that one, but the other two queries do return something and thus you get the 2 desired records.
(Of course, if the database does not support subselects, then one just has to break the subselects into individually run queries, saving the results to then manually input into the next query.)
SELECT deptno, empno, ename FROM emp
WHERE empno IN
(SELECT max(empno) FROM emp
WHERE empno < _value_
AND deptno IN (SELECT deptno FROM emp
WHERE empno = _value_)
AND sal >= 5000)
UNION
SELECT deptno, empno, ename FROM emp
WHERE empno = _value_
UNION
SELECT deptno, empno, ename FROM emp
WHERE empno IN
(SELECT MIN(empno) FROM emp
WHERE empno > _value_
AND deptno IN (SELECT deptno FROM emp
WHERE empno = _value_)
AND sal >= 5000)ASKER
ORDER BY deptno ASC, empno ASC:
deptno empno
-------- --------
10 40
10 60
20 10
20 20
20 30
30 50
30 70
30 80
Suppose, we feed empno = 10, then deptno = 20 in current record, = 10 in previous record, = 20 in next record. So, to determine whether in previous and next deptno is same/different is first task. Then within same deptno, empno is sorted in ASC order but acroos different deptno, sorting order of empno as you can see is not guaranteed.
No, we cannot use stored procedure but it could be achieved partly in one/more queries & partly in Java. Temporary tables can be used only inside stored procedure. This is a project requirement and I just gave its simple situation equivalent.
deptno empno
-------- --------
10 40
10 60
20 10
20 20
20 30
30 50
30 70
30 80
Suppose, we feed empno = 10, then deptno = 20 in current record, = 10 in previous record, = 20 in next record. So, to determine whether in previous and next deptno is same/different is first task. Then within same deptno, empno is sorted in ASC order but acroos different deptno, sorting order of empno as you can see is not guaranteed.
No, we cannot use stored procedure but it could be achieved partly in one/more queries & partly in Java. Temporary tables can be used only inside stored procedure. This is a project requirement and I just gave its simple situation equivalent.
Based on what I read in your original problem explanation, I assumed that you only wanted the previous and next records within the same department of the specified 'empno'. If this is _not_ correct (that is, you want the previous and next record regardless of department, from the original ordering), then it becomes more difficult. I'll wait for your clarification on this.
k_murli_krishna,
No, we cannot use stored procedure but it could be achieved partly in one/more queries & partly in Java. Temporary tables can be used only inside stored procedure.
Based upon the above statements, it looks like you are probably going to have to pull the data using the SQL statement and then process it using Java.
Not having stored procedures available to you and, especially, not having temporary tables available, this becomes extremely tricky. The sitcky spot is that you appear to want the preceding and succeeding DeptNo's checked regardless of the empno.
I assume that this is associated with a need to display the different departments on different pages or to put some sort of header between them but, without really knowing what you are trying to accomplish in the end, I cannot offer an alternative to the use of Java to process the data.
No, we cannot use stored procedure but it could be achieved partly in one/more queries & partly in Java. Temporary tables can be used only inside stored procedure.
Based upon the above statements, it looks like you are probably going to have to pull the data using the SQL statement and then process it using Java.
Not having stored procedures available to you and, especially, not having temporary tables available, this becomes extremely tricky. The sitcky spot is that you appear to want the preceding and succeeding DeptNo's checked regardless of the empno.
I assume that this is associated with a need to display the different departments on different pages or to put some sort of header between them but, without really knowing what you are trying to accomplish in the end, I cannot offer an alternative to the use of Java to process the data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just as an illustration of the two different solutions, I took your sample data, and added names and values for sal (all 6000), and put them into an SQLite database. Here is the table:
deptno|empno|ename|sal
10|40|alice|6000
10|60|bob|6000
20|10|carol|6000
20|20|david|6000
20|30|edward|6000
30|50|fred|6000
30|70|grace|6000
30|80|hugh|6000
When I run the first solution I gave against this set, with "10" in for _value_, here are the results I get:
20|10|carol
20|20|david
When I run the second solution I gave against this set, again with "10" in for _value_, here are the results I get:
10|60|bob
20|10|carol
20|20|david
So if neither of these is what you want, then please let me know what I'm missing.
deptno|empno|ename|sal
10|40|alice|6000
10|60|bob|6000
20|10|carol|6000
20|20|david|6000
20|30|edward|6000
30|50|fred|6000
30|70|grace|6000
30|80|hugh|6000
When I run the first solution I gave against this set, with "10" in for _value_, here are the results I get:
20|10|carol
20|20|david
When I run the second solution I gave against this set, again with "10" in for _value_, here are the results I get:
10|60|bob
20|10|carol
20|20|david
So if neither of these is what you want, then please let me know what I'm missing.
ASKER
Thank you very much.
It all hinges around the need to find whether next/previous to current deptno is same one or different one since for same deptno empno will be sorted in ASC order but across deptno's sorting order is not at all guaranteed.
Can you use a stored procedure in achieving this ?
Can you use a temporary table in achieving this ?