# Thread: Possible Use of a Cursor

1. Registered User
Join Date
Nov 2004
Posts
57

## Unanswered: Possible Use of a Cursor

I have a complex query that I hope I can explain it well enough for
everyone to understand.

I have a table that contains information for work instructions.
Contained in the table are "MACHINE_PROC" these are groupings of
machining processes (milling,drilling etc). Contained also in the table
are "INSTRUCTION" that are a grouping of machining processes in a
particular order. In order to organize the order "MACHINE_PROC" are

"INSTRUCTION" can have any number of "POSITION_NR" and
"MACHINE_PROC". "POSITION_NR" are always acending meaning that the
smallest number is at the begining of a instruction and the larger a
number towards the end.

The problem that I have is that I want to look for a given set of
machining processes (MACHINE_PROC) from groups 3500, 3400 and 3430 in
the first position of a given INSTRUCTION. The problem is that the
first step of a INSTRUCTION doesn't always have the same number.

Normally the first "POSITION_NR" of a job is "10" and the second "20"
and so on... Room is left between the steps in case that a extra step

If a step needs to be added before the first step it will get a
number below "10" and if a step needs to be added between the first and
second step it will be between "10" and "20" and so on...

Hopefully you understand me in what I'm trying to explain. Here's 2
examples to help along.

Remember I'm looking for the first step in an "INSTRUCTION" that has
"MACHINE_PROC" 3600, 3400 or 3430

Example 1
INSTRUCTION_NR | MACHINE_PROC | POSITION_NR
123456 | 3500 | 10
123456 | 5600 | 20
123456 | 4587 | 30

Example 2
INSTRUCTION_NR | MACHINE_PROC | POSITION_NR
456789 | 3500 | 5
456789 | 6522 | 10
456789 | 7841 | 20

Does any one know how to go about this do I need to write a cursor of
can I do this by using a couple select statements.

P.S. This is not homework. This is a real problem that I have. I took
the time to write this post, please don't respond to it saying
it's homework or that I should read the manual.

2. Registered User
Join Date
Mar 2002
Location
Posts
1,137
Could you provide the example data from the tables and also how the corresponding output would look as it will probably be a bit easier then.

Alan

3. Drunkard
Join Date
Nov 2002
Location
Desk, slightly south of keyboard
Posts
697
Hi,

Does this help?

Code:
```select t.instruction_nr, t.machine_proc, t.position_nr
from   test t,
(
select instruction_nr, min( position_nr ) position_nr
from   test
group by instruction_nr
) v
where  t.instruction_nr = v.instruction_nr and
t.position_nr    = v.position_nr    and
t.machine_proc in (3500,3400,3430)```
Hth
Bill

4. Registered User
Join Date
Nov 2004
Posts
57
Sure Alan,

I can provide information part of a dump from the table but I'll have to translate it to English first so that we can understand it better. The database is in German.

5. Registered User
Join Date
Nov 2004
Posts
57
So now I have a solution for finding the first step of all the instructions I want. I used the following query:

Code:
```select
distinct(papp.INSTRUCTION_NR),papp.MACHINE_PROC,papp.POSITIONS_NR
from w100.papp,
(select min (papp.POSITIONS_NR)
from w100.papp)
where papp.MACHINE_PROC in (3530,3430,3500)```
Now I'm interested in finding out the second step in an INSTRUCTION can I do something like MIN + 1 to get the 2nd step and MIN + 2 to get the 3rd step and so on... Is this possible?

6. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Sounds like an application for the RANK (or DENSE_RANK) analytic function:

Code:
```SQL> select ename
2  ,      sal
3  ,      rank() over (order by sal) rnk
4  ,      dense_rank() over (order by sal) drnk
5  from emp;

ENAME             SAL        RNK       DRNK
---------- ---------- ---------- ----------
SMITH             800          1          1
JAMES             950          2          2
WARD             1250          4          4
MARTIN           1250          4          4
MILLER           1300          6          5
TURNER           1500          7          6
ALLEN            1600          8          7
CLARK            2450          9          8
BLAKE            2850         10          9
JONES            2975         11         10
SCOTT            3000         12         11
XXX              3000         12         11
FORD             3000         12         11
KING             5250         15         12```
Note the difference between the 2 functions when there are ties, e.g. WARD and MARTIN. The subsequent rows then get a different value depending which function is used.

7. Registered User
Join Date
Nov 2004
Posts
57
Thanks Tony,

But I don't think that will work for me because I don't have "OLAP Window Functions" eneabled on my database. Is there another way to go about this without using Analytical Functions?

8. Drunkard
Join Date
Nov 2002
Location
Desk, slightly south of keyboard
Posts
697
Hi Paul,

Your query is returning different results to mine, the test data I used is..

Code:
```INSTRUCTION_NR, MACHINE_PROC, POSITION_NR
123456	3500	10
123456	5600	20
123456	4587	30
456789	3500	5
456789	6522	10
456789	7841	20
234567	1010	10
234567	3500	20```
Your query is showing 234567,3500,20 which (if I understand your original post) is wrong as you wanted the machine procs which were the first in the position_nr list.

You could modify my original query to return machine proc's in second place, but if you started wanting third place etc it would get a bit untidy.

Hth
Bill

9. Registered User
Join Date
Nov 2004
Posts
57
Bill,

I haven't found your concern in any of the output from my query. The INSTRUCTION numbers that I got in return had as the first step of a task MACHINE_PROC groups 3500,3430 and 3400.

I haven't had any success with interpreting your initial post into a query but if it's capable of calling the second or third position then I'll have to give it another look into.

10. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Originally Posted by Paul Izzo
Thanks Tony,

But I don't think that will work for me because I don't have "OLAP Window Functions" eneabled on my database. Is there another way to go about this without using Analytical Functions?
Pity. In that case you could try this approach:
Highest:
Code:
```SQL> select e1.ename, e1.sal
2  from emp e1
3  where 0 = (select count(distinct sal) from emp e2 where e2.sal > e1.sal);

ENAME             SAL
---------- ----------
KING             5250```
Second highest:
Code:
```SQL> select e1.ename, e1.sal
2  from emp e1
3  where 1 = (select count(distinct sal) from emp e2 where e2.sal > e1.sal);

ENAME             SAL
---------- ----------
SCOTT            3000
FORD             3000```
Third highest:
Code:
```SQL> select e1.ename, e1.sal
2  from emp e1
3  where 2 = (select count(distinct sal) from emp e2 where e2.sal > e1.sal);

ENAME             SAL
---------- ----------
JONES            2975```
...etc.

But I think if you want to find the first step, then the second, then the third... then what you reall want is a simple SELECT statement with an ORDER BY!:
Code:
```SQL> select ename, sal from emp
2  order by sal desc;

ENAME             SAL
---------- ----------
KING             5250
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850
CLARK            2450
ALLEN            1600
TURNER           1500
MILLER           1300
WARD             1250
MARTIN           1250
JAMES             950
SMITH             800```

11. Drunkard
Join Date
Nov 2002
Location
Desk, slightly south of keyboard
Posts
697
Hi Paul,

I'm using the following data.
Code:
```select * from test order by 1,3,2

INSTRU  MACH    PO
======  ====    ==
123456	3500	10
123456	5600	20
123456	4587	30
234567	1010	10
234567	3500	20
456789	3500	5
456789	6522	10
456789	7841	20```
Correct results for 3500,3400,3430 as first placed POSITION_NR would be
Code:
```INSTRU  MACH    PO
======  ====    ==
123456	3500	10
456789	3500	5```
Code:
```INSTRU  MACH    PO
======  ====    ==
123456	3500	10
234567	3500	20
456789	3500	5```
The row 234567,3500,20 is incorrect. My query returns
Code:
```INSTRU  MACH    PO
======  ====    ==
123456	3500	10
456789	3500	5```
Which I believe is correct.

To get rows where your list of MACHINE_PROC is the second placed entry, you can do the following
Code:
```select t.instruction_nr, t.machine_proc, t.position_nr
from   test t,
(
select instruction_nr, min( position_nr ) position_nr
from   test
where  (instruction_nr, position_nr) not in (
select instruction_nr, min( position_nr ) position_nr
from   test
group by instruction_nr
)
group by instruction_nr
) v
where  t.instruction_nr = v.instruction_nr and
t.position_nr    = v.position_nr    and
t.machine_proc in (3500,3400,3430)

INSTRU  MACH    PO
======  ====    ==
234567	3500	20```
As I said, it would get quite messy for third, fourth etc etc.

Tony's solution using Analytics is the most elegant, but a shame you don't have them available.

Hth
Bill

12. Registered User
Join Date
Nov 2004
Posts
57
I created a second database on a seperate machine with OLAP installed. Now I can use analytical functions. I get rank to work for me using the following:

Code:
```select papp.INSTRUCTION_NR,
papp.MACHINE_PROC,
rank() over (order by papp.POSITIONS_NR) rnk
from w100.papp
where papp.INSTRUCTION_NR = '0701-070000-02'```
I get the following output:

Instruction NR | MACHINE_PROC | Rnk
0701-070000-02 3430 1
0701-070000-02 3530 2
0701-070000-02 4100 3
0701-070000-02 4200 4
0701-070000-02 5850 5
0701-070000-02 8860 6

What I'm looking to do is get the 2nd rank of every "Instruction NR" that has Machine_Proc '3530'

But I get this to work only when I run the query with a specific "Instruction_NR"

I tried the following query that looks like this but I don't get anything in return:

Code:
```select papp.INSTRUCTION_NR,
papp.MACHINE_PROC
from w100.papp,
(select papp.INSTRUCTION_NR,
papp.MACHINE_PROC,
rank() over (order by papp.MACHINE_PROC) rnk
from w100.papp)x
where x.rnk = 2```

13. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Use the PARTITION BY clause:
Code:
```select papp.INSTRUCTION_NR,
papp.MACHINE_PROC,
rank() over (partition by papp.MACHINE_PROC order by papp.POSITIONS_NR) rnk
from w100.papp
where papp.INSTRUCTION_NR = '0701-070000-02'```

14. Registered User
Join Date
Nov 2004
Posts
57
Thanks Tony,

I made my query with the following code and it worked, I'll give yours a try and see if I get the same results.

Code:
```select dinstinct(papp.INSTRUCTION_NR),
papp.MACHINE_PROC,
papp.positions_nr
from w100.papp,
(select
row_number() over (partition by papp.INSTRUCTION_NR
order by papp.positions_nr) rn
from w100.papp)x
where x.rn = 3
and papp.MACHINE_PROC = '8860'```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•