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

    Question Unanswered: Problems with formulating a cursor

    I have 2 tables in a database that contain customer transaction data. The first table (PKVK)contains customer transaction header information. The second table (PKVP) contains work order information.

    A customer can have one transaction with several work orders. Example would be a customer transaction for 10 machines. 1 transaction would contain 10 seperate work orders.

    Customer transactions and work orders are given a status of 1 to 9, 9 means complete. All other status's are open status of some sort.

    I want to search the database for customer transactions that are not complete ( Customer_Transaction_Status != 9) and compare them against work orders that are completed.

    The following is the cursor that I'm working on peprhaps someone can say what it is that I'm doing wrong.



    DECLARE
    -- pos_stat pkvp.POSITIONS_STATUS


    cursor position
    is
    select pkvp.Customer_Transaction_Nr,pkvp.POSITIONS_NR,pkv p.POSITIONS_STATUS,pkvp.Customer_Nr,pkvk.Customer_ Transaction_Status
    from pkvp,pkvk
    where pkvk.Customer_Transaction_Status != 9
    and pkvp.Customer_Transaction_Nr = pkvk.Customer_Transaction_Nr
    and pkvp.Customer_Transaction_Type = pkvk.Customer_Transaction_Type
    pos_rec position%ROWTYPE;

    BEGIN
    OPEN position;
    LOOP
    FETCH position into pos_rec;
    EXIT when position%NOTFOUND;
    where pkvp.POSITIONS_STATUS = 9
    if v_count!=9 then exit
    commit;
    END LOOP;
    commit;
    CLOSE position;
    END;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    (This question should have been posted in the Oracle forum BTW)

    Your code is all over the place! IF starements begin but never end, WHERE clauses appear out of nowhere, you COMMIT again and again but haven't inserted, updated or deleted anything, ...

    I think your current code can be boiled down to this:
    Code:
    DECLARE
    
      cursor position is
        select pkvp.Customer_Transaction_Nr,pkvp.POSITIONS_NR,pkvp.POSITIONS_STATUS,pkvp.Customer_Nr,
               pkvk.Customer_Transaction_Status
        from   pkvp,pkvk
        where  pkvk.Customer_Transaction_Status != 9
        and    pkvp.Customer_Transaction_Nr = pkvk.Customer_Transaction_Nr
        and    pkvp.Customer_Transaction_Type = pkvk.Customer_Transaction_Type
        and    pkvp.POSITIONS_STATUS = 9;
    
      pos_rec position%ROWTYPE;
    
    BEGIN
      OPEN position;
      LOOP
        FETCH position into pos_rec;
        EXIT when position%NOTFOUND;
      END LOOP;
      CLOSE position;
    END;
    It is even simpler if you use a FOR loop:
    Code:
    DECLARE
    
      cursor position is
        select pkvp.Customer_Transaction_Nr,pkvp.POSITIONS_NR,pkvp.POSITIONS_STATUS,pkvp.Customer_Nr,
               pkvk.Customer_Transaction_Status
        from   pkvp,pkvk
        where  pkvk.Customer_Transaction_Status != 9
        and    pkvp.Customer_Transaction_Nr = pkvk.Customer_Transaction_Nr
        and    pkvp.Customer_Transaction_Type = pkvk.Customer_Transaction_Type
        and    pkvp.POSITIONS_STATUS = 9;
    
    BEGIN
      FOR pos_rec IN position
      LOOP
        NULL; -- Because you have to do something!
      END LOOP;
    END;
    In the second case I had to add the "NULL;" statement because you are selecting records but not actually processing them - e.g. writing out the data to the screen or whatever.

    Great table names by the way (not!)

  3. #3
    Join Date
    Nov 2004
    Posts
    57

    Thumbs down

    Thanks Mr. Moderator but your suggestions didn't work. My tablespace names have been translated from German to English in order for me to formulate a question that the forum will understand.

    Your first suggestion returns an error "ORA-00900: invalid SQL statement" and the second suggestion doen't do anything.

    As for my code I know that it was sloppy and wrong but, that's why I submitted it to the forum for other's to view it and give their suggestions.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by Paul Izzo
    Thanks Mr. Moderator but your suggestions didn't work. My tablespace names have been translated from German to English in order for me to formulate a question that the forum will understand.
    I'd prefer table names like TRANSACTION_HEADER and WORK_ORDER to PKVK and PKVP personally!

    Quote Originally Posted by Paul Izzo
    Your first suggestion returns an error "ORA-00900: invalid SQL statement" and the second suggestion doen't do anything.
    I don't see why you should get the ORA-00900 on the first but not on the second, as the SQL is identical. I know the second one doesn't do anything - in fact I said as much. The thing is, I don't know what you want to do. If you just want some output try this:
    Code:
    SET SERVEROUT ON SIZE 1000000
    
    DECLARE
    
      cursor position is
        select pkvp.Customer_Transaction_Nr,pkvp.POSITIONS_NR,pkv  p.POSITIONS_STATUS,pkvp.Customer_Nr,
               pkvk.Customer_Transaction_Status
        from   pkvp,pkvk
        where  pkvk.Customer_Transaction_Status != 9
        and    pkvp.Customer_Transaction_Nr = pkvk.Customer_Transaction_Nr
        and    pkvp.Customer_Transaction_Type = pkvk.Customer_Transaction_Type
        and    pkvp.POSITIONS_STATUS = 9;
    
    BEGIN
      FOR pos_rec IN position
      LOOP
        DBMS_OUTPUT.PUT_LINE(pkvp.Customer_Transaction_Nr);
      END LOOP;
    END;
    /
    In fact, if you just want some output simply do this in SQL Plus:
    Code:
    select pkvp.Customer_Transaction_Nr,pkvp.POSITIONS_NR,pkv  p.POSITIONS_STATUS,pkvp.Customer_Nr,
               pkvk.Customer_Transaction_Status
        from   pkvp,pkvk
        where  pkvk.Customer_Transaction_Status != 9
        and    pkvp.Customer_Transaction_Nr = pkvk.Customer_Transaction_Nr
        and    pkvp.Customer_Transaction_Type = pkvk.Customer_Transaction_Type
        and    pkvp.POSITIONS_STATUS = 9;
    Quote Originally Posted by Paul Izzo
    As for my code I know that it was sloppy and wrong but, that's why I submitted it to the forum for other's to view it and give their suggestions.
    Generally, it is preferable to ensure that your code is neat and syntactically correct before submitting it - makes it easier if you help us to help you

Posting Permissions

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