Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2004
    Posts
    57

    Question 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
    made they become a "POSITION_NR".

    "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
    needs to be added.

    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. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    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. #3
    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
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  4. #4
    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. #5
    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. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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
    ADAMS            1100          3          3
    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. #7
    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. #8
    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
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  9. #9
    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. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote 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
    ADAMS            1100
    JAMES             950
    SMITH             800

  11. #11
    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
    Your query is returning...
    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
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  12. #12
    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. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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. #14
    Join Date
    Nov 2004
    Posts
    57

    Smile

    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
  •